+-------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------------+---------+----------+----------+ | mariadb_ge_le_1000_notnull.t1 | analyze | status | OK | | mariadb_ge_le_1000_notnull.t2 | analyze | status | OK | | mariadb_ge_le_1000_notnull.t3 | analyze | status | OK | +-------------------------------+---------+----------+----------+ +------------------------------------------+ | phase | +------------------------------------------+ | 0. version, default settings, row counts | +------------------------------------------+ +-----------------+ | mariadb_version | +-----------------+ | 12.3.2-MariaDB | +-----------------+ +-----------------------+------------------------+------------------+ | optimizer_prune_level | optimizer_search_depth | join_cache_level | +-----------------------+------------------------+------------------+ | 2 | 62 | 2 | +-----------------------+------------------------+------------------+ +------------+-----------+---------+---------+ | table_name | row_count | min_c1 | max_c1 | +------------+-----------+---------+---------+ | t1 | 1000 | 1000001 | 1001000 | | t2 | 1000 | 2000011 | 3000010 | | t3 | 1000 | 3000001 | 3001000 | +------------+-----------+---------+---------+ +------------------------+ | phase | +------------------------+ | 1. join-overlap sanity | +------------------------+ +---------------+---------------+-----------------+ | t1_t3_eq_rows | t2_t3_eq_rows | final_join_rows | +---------------+---------------+-----------------+ | 1000 | 10 | 10 | +---------------+---------------+-----------------+ +----------------------------------+ | phase | +----------------------------------+ | 2. safe result equivalence check | +----------------------------------+ +---------------+-------------+----------+ | variant | result_rows | checksum | +---------------+-------------+----------+ | EQUALITY_FORM | 10 | 40000110 | | BETWEEN_EQUIV | 10 | 40000110 | | NOT_NE_EQUIV | 10 | 40000110 | +---------------+-------------+----------+ +-------------------------------------+ | phase | +-------------------------------------+ | 3. default optimizer: equality form | +-------------------------------------+ +------+-------------+-------+-------+----------------------------+------------+---------+----------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+----------------------------+------------+---------+----------------------------------+------+--------------------------+ | 1 | SIMPLE | t3 | index | i_t3_c0,i_t3_c1,i_t3_c0_c1 | i_t3_c0_c1 | 16 | NULL | 1000 | Using where; Using index | | 1 | SIMPLE | t1 | ref | i_t1_c1 | i_t1_c1 | 8 | mariadb_ge_le_1000_notnull.t3.c0 | 1 | Using index | | 1 | SIMPLE | t2 | ref | i_t2_c1 | i_t2_c1 | 8 | mariadb_ge_le_1000_notnull.t3.c1 | 1 | Using index | +------+-------------+-------+-------+----------------------------+------------+---------+----------------------------------+------+--------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 2.10427252, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "index", "possible_keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "key": "i_t3_c0_c1", "key_length": "16", "used_key_parts": ["c0", "c1"], "loops": 1, "rows": 1000, "cost": 0.15418572, "filtered": 100, "attached_condition": "t3.c1 <= 1596112929", "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i_t1_c1"], "key": "i_t1_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c0"], "loops": 1000, "rows": 1, "cost": 0.9750434, "filtered": 100, "using_index": true } }, { "table": { "table_name": "t2", "access_type": "ref", "possible_keys": ["i_t2_c1"], "key": "i_t2_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c1"], "loops": 1000, "rows": 1, "cost": 0.9750434, "filtered": 100, "using_index": true } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.084374565 }, "query_block": { "select_id": 1, "cost": 2.10427252, "r_loops": 1, "r_total_time_ms": 3.052495849, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "index", "possible_keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "key": "i_t3_c0_c1", "key_length": "16", "used_key_parts": ["c0", "c1"], "loops": 1, "r_loops": 1, "rows": 1000, "r_rows": 1000, "cost": 0.15418572, "r_table_time_ms": 0.365088754, "r_other_time_ms": 0.140640718, "r_engine_stats": { "pages_accessed": 7 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c1 <= 1596112929", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i_t1_c1"], "key": "i_t1_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c0"], "loops": 1000, "r_loops": 1000, "rows": 1, "r_rows": 1, "cost": 0.9750434, "r_table_time_ms": 1.299353693, "r_other_time_ms": 0.146368908, "r_engine_stats": { "pages_accessed": 2002 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t2", "access_type": "ref", "possible_keys": ["i_t2_c1"], "key": "i_t2_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c1"], "loops": 1000, "r_loops": 1000, "rows": 1, "r_rows": 0.01, "cost": 0.9750434, "r_table_time_ms": 1.051685981, "r_other_time_ms": 0.046588243, "r_engine_stats": { "pages_accessed": 2003 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------+ | phase | +-------------------------------------------------------+ | 4. default optimizer: BETWEEN x AND x equivalent form | +-------------------------------------------------------+ +------+-------------+-------+-------+----------------------------+------------+---------+----------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+----------------------------+------------+---------+----------------------------------+------+--------------------------+ | 1 | SIMPLE | t3 | index | i_t3_c0,i_t3_c1,i_t3_c0_c1 | i_t3_c0_c1 | 16 | NULL | 1000 | Using index | | 1 | SIMPLE | t1 | ref | i_t1_c1 | i_t1_c1 | 8 | mariadb_ge_le_1000_notnull.t3.c0 | 1 | Using where; Using index | | 1 | SIMPLE | t2 | ref | i_t2_c1 | i_t2_c1 | 8 | mariadb_ge_le_1000_notnull.t3.c1 | 1 | Using where; Using index | +------+-------------+-------+-------+----------------------------+------------+---------+----------------------------------+------+--------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 2.10427252, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "index", "possible_keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "key": "i_t3_c0_c1", "key_length": "16", "used_key_parts": ["c0", "c1"], "loops": 1, "rows": 1000, "cost": 0.15418572, "filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i_t1_c1"], "key": "i_t1_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c0"], "loops": 1000, "rows": 1, "cost": 0.9750434, "filtered": 100, "attached_condition": "t3.c0 between t1.c1 and t1.c1", "using_index": true } }, { "table": { "table_name": "t2", "access_type": "ref", "possible_keys": ["i_t2_c1"], "key": "i_t2_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c1"], "loops": 1000, "rows": 1, "cost": 0.9750434, "filtered": 100, "attached_condition": "t2.c1 <= 1596112929 and t3.c1 between t2.c1 and t2.c1", "using_index": true } } ] } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.073312792 }, "query_block": { "select_id": 1, "cost": 2.10427252, "r_loops": 1, "r_total_time_ms": 2.800028917, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "index", "possible_keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "key": "i_t3_c0_c1", "key_length": "16", "used_key_parts": ["c0", "c1"], "loops": 1, "r_loops": 1, "rows": 1000, "r_rows": 1000, "cost": 0.15418572, "r_table_time_ms": 0.328598604, "r_other_time_ms": 0.096263916, "r_engine_stats": { "pages_accessed": 4 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i_t1_c1"], "key": "i_t1_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c0"], "loops": 1000, "r_loops": 1000, "rows": 1, "r_rows": 1, "cost": 0.9750434, "r_table_time_ms": 1.205480252, "r_other_time_ms": 0.161524724, "r_engine_stats": { "pages_accessed": 2002 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c0 between t1.c1 and t1.c1", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t2", "access_type": "ref", "possible_keys": ["i_t2_c1"], "key": "i_t2_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c1"], "loops": 1000, "r_loops": 1000, "rows": 1, "r_rows": 0.01, "cost": 0.9750434, "r_table_time_ms": 0.951371866, "r_other_time_ms": 0.05373866, "r_engine_stats": { "pages_accessed": 2003 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t2.c1 <= 1596112929 and t3.c1 between t2.c1 and t2.c1", "r_filtered": 100, "using_index": true } } ] } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------------------+ | phase | +------------------------------------------------------+ | 5. default optimizer: NOT <> equivalent control form | +------------------------------------------------------+ +------+-------------+-------+-------+----------------------------+------------+---------+----------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+----------------------------+------------+---------+----------------------------------+------+--------------------------+ | 1 | SIMPLE | t3 | index | i_t3_c0,i_t3_c1,i_t3_c0_c1 | i_t3_c0_c1 | 16 | NULL | 1000 | Using where; Using index | | 1 | SIMPLE | t1 | ref | i_t1_c1 | i_t1_c1 | 8 | mariadb_ge_le_1000_notnull.t3.c0 | 1 | Using index | | 1 | SIMPLE | t2 | ref | i_t2_c1 | i_t2_c1 | 8 | mariadb_ge_le_1000_notnull.t3.c1 | 1 | Using index | +------+-------------+-------+-------+----------------------------+------------+---------+----------------------------------+------+--------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 2.10427252, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "index", "possible_keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "key": "i_t3_c0_c1", "key_length": "16", "used_key_parts": ["c0", "c1"], "loops": 1, "rows": 1000, "cost": 0.15418572, "filtered": 100, "attached_condition": "t3.c1 <= 1596112929", "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i_t1_c1"], "key": "i_t1_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c0"], "loops": 1000, "rows": 1, "cost": 0.9750434, "filtered": 100, "using_index": true } }, { "table": { "table_name": "t2", "access_type": "ref", "possible_keys": ["i_t2_c1"], "key": "i_t2_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c1"], "loops": 1000, "rows": 1, "cost": 0.9750434, "filtered": 100, "using_index": true } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.069425186 }, "query_block": { "select_id": 1, "cost": 2.10427252, "r_loops": 1, "r_total_time_ms": 3.119969011, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "index", "possible_keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "key": "i_t3_c0_c1", "key_length": "16", "used_key_parts": ["c0", "c1"], "loops": 1, "r_loops": 1, "rows": 1000, "r_rows": 1000, "cost": 0.15418572, "r_table_time_ms": 0.369708026, "r_other_time_ms": 0.149087309, "r_engine_stats": { "pages_accessed": 7 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c1 <= 1596112929", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i_t1_c1"], "key": "i_t1_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c0"], "loops": 1000, "r_loops": 1000, "rows": 1, "r_rows": 1, "cost": 0.9750434, "r_table_time_ms": 1.33573697, "r_other_time_ms": 0.147613014, "r_engine_stats": { "pages_accessed": 2002 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t2", "access_type": "ref", "possible_keys": ["i_t2_c1"], "key": "i_t2_c1", "key_length": "8", "used_key_parts": ["c1"], "ref": ["mariadb_ge_le_1000_notnull.t3.c1"], "loops": 1000, "r_loops": 1000, "rows": 1, "r_rows": 0.01, "cost": 0.9750434, "r_table_time_ms": 1.07016581, "r_other_time_ms": 0.04535327, "r_engine_stats": { "pages_accessed": 2003 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------+ | phase | +-------------------------------------------------+ | 6. default optimizer: >= and <= equivalent form | +-------------------------------------------------+ +------+-------------+-------+-------+----------------------------+---------+---------+------+------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+----------------------------+---------+---------+------+------+--------------------------------------------------------------+ | 1 | SIMPLE | t1 | index | i_t1_c1 | i_t1_c1 | 8 | NULL | 1000 | Using index | | 1 | SIMPLE | t2 | range | i_t2_c1 | i_t2_c1 | 8 | NULL | 1000 | Using where; Using index; Using join buffer (flat, BNL join) | | 1 | SIMPLE | t3 | ALL | i_t3_c0,i_t3_c1,i_t3_c0_c1 | NULL | NULL | NULL | 1000 | Range checked for each record (index map: 0x7) | +------+-------------+-------+-------+----------------------------+---------+---------+------+------+--------------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 153895.5566, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i_t1_c1"], "key": "i_t1_c1", "key_length": "8", "used_key_parts": ["c1"], "loops": 1, "rows": 1000, "cost": 0.15418572, "filtered": 100, "using_index": true } }, { "block-nl-join": { "table": { "table_name": "t2", "access_type": "range", "possible_keys": ["i_t2_c1"], "key": "i_t2_c1", "key_length": "8", "used_key_parts": ["c1"], "loops": 1000, "rows": 1000, "cost": 93.07684284, "filtered": 100, "attached_condition": "t2.c1 <= 1596112929", "using_index": true }, "buffer_type": "flat", "buffer_size": "8KiB", "join_type": "BNL" } }, { "range-checked-for-each-record": { "keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "loops": 1000000, "rows": 1000, "cost": 153802.3256, "filtered": 100 } } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.07760962 }, "query_block": { "select_id": 1, "cost": 153895.5566, "r_loops": 1, "r_total_time_ms": 10199.38423, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i_t1_c1"], "key": "i_t1_c1", "key_length": "8", "used_key_parts": ["c1"], "loops": 1, "r_loops": 1, "rows": 1000, "r_rows": 1000, "cost": 0.15418572, "r_table_time_ms": 0.318818389, "r_other_time_ms": 0.096146996, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "block-nl-join": { "table": { "table_name": "t2", "access_type": "range", "possible_keys": ["i_t2_c1"], "key": "i_t2_c1", "key_length": "8", "used_key_parts": ["c1"], "loops": 1000, "r_loops": 1, "rows": 1000, "r_rows": 1000, "cost": 93.07684284, "r_table_time_ms": 0.768496367, "r_other_time_ms": 0.396370462, "r_engine_stats": { "pages_accessed": 6 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t2.c1 <= 1596112929", "r_filtered": 100, "using_index": true }, "buffer_type": "flat", "buffer_size": "8KiB", "join_type": "BNL", "r_loops": 1000, "r_filtered": 100, "r_unpack_time_ms": 35.16409169, "r_other_time_ms": 9054.937746, "r_effective_rows": 1000 } }, { "range-checked-for-each-record": { "keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "r_keys": { "full_scan": 0, "index_merge": 0, "range": { "i_t3_c0": 0, "i_t3_c1": 0, "i_t3_c0_c1": 1000000 } }, "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i_t3_c0", "i_t3_c1", "i_t3_c0_c1"], "loops": 1000000, "r_loops": 1000000, "rows": 1000, "r_rows": 0.00001, "cost": 153802.3256, "r_table_time_ms": 1031.25798, "r_other_time_ms": 76.44207381, "r_engine_stats": { "pages_accessed": 8003000 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } } ] } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------+ | phase | +-----------------------+ | 7. GE/LE result check | +-----------------------+ +-------------+-------------+----------+ | variant | result_rows | checksum | +-------------+-------------+----------+ | GE_LE_EQUIV | 10 | 40000110 | +-------------+-------------+----------+ +-----------------------+ | phase | +-----------------------+ | 8. sample result rows | +-----------------------+ +---------+---------+ | ref0 | ref1 | +---------+---------+ | 1000001 | 3000001 | | 1000002 | 3000002 | | 1000003 | 3000003 | | 1000004 | 3000004 | | 1000005 | 3000005 | | 1000006 | 3000006 | | 1000007 | 3000007 | | 1000008 | 3000008 | | 1000009 | 3000009 | | 1000010 | 3000010 | +---------+---------+ +-------+ | phase | +-------+ | done | +-------+