+----------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------------+---------+----------+----------+ | mariadb1232qpg10y701270.t0 | analyze | status | OK | | mariadb1232qpg10y701270.t1 | analyze | status | OK | +----------------------------+---------+----------+----------+ +----------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------------------+---------+----------+-----------------------------------------+ | mariadb1232qpg10y701270.t0 | analyze | status | Engine-independent statistics collected | | mariadb1232qpg10y701270.t0 | analyze | status | OK | +----------------------------+---------+----------+-----------------------------------------+ +----------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------------------+---------+----------+-----------------------------------------+ | mariadb1232qpg10y701270.t1 | analyze | status | Engine-independent statistics collected | | mariadb1232qpg10y701270.t1 | analyze | status | OK | +----------------------------+---------+----------+-----------------------------------------+ +-------------------------+ | marker | +-------------------------+ | ===== environment ===== | +-------------------------+ +----------------+-----------------+ | version | version_comment | +----------------+-----------------+ | 12.3.2-MariaDB | MariaDB Server | +----------------+-----------------+ +-------------------------------------+------------------------+ | Variable_name | Value | +-------------------------------------+------------------------+ | histogram_size | 254 | | histogram_type | JSON_HB | | join_buffer_size | 262144 | | max_statement_time | 30.000000 | | optimizer_join_limit_pref_ratio | 0 | | optimizer_prune_level | 0 | | optimizer_search_depth | 62 | | optimizer_use_condition_selectivity | 4 | | use_stat_tables | PREFERABLY_FOR_QUERIES | +-------------------------------------+------------------------+ +--------------------------------------------+ | marker | +--------------------------------------------+ | ===== table/index/cardinality sanity ===== | +--------------------------------------------+ +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | t0 | 1 | i0 | 1 | c0 | A | 200000 | NULL | NULL | YES | BTREE | | | NO | | t0 | 1 | i1 | 1 | c0 | A | 200000 | NULL | NULL | YES | BTREE | | | NO | | t0 | 1 | i1 | 2 | c1 | A | 200000 | NULL | NULL | YES | BTREE | | | NO | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | t1 | 1 | i2 | 1 | c0 | A | 200000 | NULL | NULL | YES | BTREE | | | NO | | t1 | 1 | i3 | 1 | c0 | A | 200000 | NULL | NULL | YES | BTREE | | | NO | | t1 | 1 | i3 | 2 | c1 | A | 200000 | NULL | NULL | YES | BTREE | | | NO | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ +------------+--------+--------+--------+--------+ | table_name | rows_n | ndv_c0 | min_c0 | max_c0 | +------------+--------+--------+--------+--------+ | t0 | 200000 | 200000 | 1000 | 200999 | | t1 | 200000 | 200000 | 1000 | 200999 | +------------+--------+--------+--------+--------+ +-------------------------------------+ | marker | +-------------------------------------+ | ===== predicate/result sanity ===== | +-------------------------------------+ +---------------+-----------------+---------------+--------------+--------------+ | total_t1_rows | matched_t1_rows | matched_ratio | min_match_c0 | max_match_c0 | +---------------+-----------------+---------------+--------------+--------------+ | 200000 | 5 | 0.0000 | 1000 | 1004 | +---------------+-----------------+---------------+--------------+--------------+ +------+-----------+ | c0 | join_rows | +------+-----------+ | 1004 | 1 | | 1003 | 1 | | 1002 | 1 | | 1001 | 1 | | 1000 | 1 | +------+-----------+ +----------------------------------------------------------------------------------+ | marker | +----------------------------------------------------------------------------------+ | ################################################################################ | +----------------------------------------------------------------------------------+ +-----------------------------------+ | marker | +-----------------------------------+ | ===== ratio=0 BASE: EXPLAIN ===== | +-----------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 398.6052868, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 199215, "cost": 33.349402, "filtered": 100, "index_condition": "t0.c0 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg10y701270.t0.c0"], "loops": 200000, "rows": 1, "cost": 365.2558848, "filtered": 100, "attached_condition": "t1.c6 = '' or t1.c1 = 0" } } ] } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------+ | marker | +-----------------------------------+ | ===== ratio=0 BASE: ANALYZE ===== | +-----------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.10382721 }, "query_block": { "select_id": 1, "cost": 398.6052868, "r_loops": 1, "r_total_time_ms": 770.3745928, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 199215, "r_index_rows": 200000, "r_rows": 200000, "cost": 33.349402, "r_table_time_ms": 276.2284484, "r_other_time_ms": 19.21779299, "r_engine_stats": { "pages_accessed": 400278 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg10y701270.t0.c0"], "loops": 200000, "r_loops": 200000, "rows": 1, "r_rows": 1, "cost": 365.2558848, "r_table_time_ms": 437.1783222, "r_other_time_ms": 37.74544734, "r_engine_stats": { "pages_accessed": 1000504 }, "filtered": 100, "r_total_filtered": 0.0025, "attached_condition": "t1.c6 = '' or t1.c1 = 0", "r_filtered": 0.0025 } } ] } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Handler_read_first | 0 | | Handler_read_key | 200000 | | Handler_read_last | 1 | | Handler_read_next | 200000 | | Handler_read_prev | 200000 | | Handler_read_retry | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 0 | +--------------------------+--------+ +-------------------------------------------------------+ | marker | +-------------------------------------------------------+ | ===== ratio=0 HINTED JOIN_ORDER(t1,t0): EXPLAIN ===== | +-------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 533.6416582, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 199018, "cost": 33.6793548, "filtered": 100, "index_condition": "t1.c0 is not null", "attached_condition": "t1.c6 = '' or t1.c1 = 0" } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg10y701270.t1.c0"], "loops": 200000, "rows": 1, "cost": 364.9404928, "filtered": 100 } } ] } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------+ | marker | +-------------------------------------------------------+ | ===== ratio=0 HINTED JOIN_ORDER(t1,t0): ANALYZE ===== | +-------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.099733166 }, "query_block": { "select_id": 1, "cost": 533.6416582, "r_loops": 1, "r_total_time_ms": 310.4196722, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 199018, "r_index_rows": 200000, "r_rows": 200000, "cost": 33.6793548, "r_table_time_ms": 289.9555353, "r_other_time_ms": 20.43089317, "r_engine_stats": { "pages_accessed": 600278 }, "filtered": 100, "r_total_filtered": 0.0025, "index_condition": "t1.c0 is not null", "r_icp_filtered": 100, "attached_condition": "t1.c6 = '' or t1.c1 = 0", "r_filtered": 0.0025 } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg10y701270.t1.c0"], "loops": 200000, "r_loops": 5, "rows": 1, "r_rows": 1, "cost": 364.9404928, "r_table_time_ms": 0.017744509, "r_other_time_ms": 0.011017015, "r_engine_stats": { "pages_accessed": 20 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } ] } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Handler_read_first | 0 | | Handler_read_key | 5 | | Handler_read_last | 1 | | Handler_read_next | 5 | | Handler_read_prev | 200000 | | Handler_read_retry | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 0 | +--------------------------+--------+ +----------------------------------------------------------------------------------+ | marker | +----------------------------------------------------------------------------------+ | ################################################################################ | +----------------------------------------------------------------------------------+ +-----------------------------------+ | marker | +-----------------------------------+ | ===== ratio=1 BASE: EXPLAIN ===== | +-----------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 398.6052868, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 199215, "cost": 33.349402, "filtered": 100, "index_condition": "t0.c0 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg10y701270.t0.c0"], "loops": 200000, "rows": 1, "cost": 365.2558848, "filtered": 100, "attached_condition": "t1.c6 = '' or t1.c1 = 0" } } ] } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------+ | marker | +-----------------------------------+ | ===== ratio=1 BASE: ANALYZE ===== | +-----------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.09361585 }, "query_block": { "select_id": 1, "cost": 398.6052868, "r_loops": 1, "r_total_time_ms": 768.6965209, "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 199215, "r_index_rows": 200000, "r_rows": 200000, "cost": 33.349402, "r_table_time_ms": 276.1982528, "r_other_time_ms": 19.18836922, "r_engine_stats": { "pages_accessed": 400278 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg10y701270.t0.c0"], "loops": 200000, "r_loops": 200000, "rows": 1, "r_rows": 1, "cost": 365.2558848, "r_table_time_ms": 438.467059, "r_other_time_ms": 34.83862801, "r_engine_stats": { "pages_accessed": 1000504 }, "filtered": 100, "r_total_filtered": 0.0025, "attached_condition": "t1.c6 = '' or t1.c1 = 0", "r_filtered": 0.0025 } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Handler_read_first | 0 | | Handler_read_key | 200000 | | Handler_read_last | 1 | | Handler_read_next | 200000 | | Handler_read_prev | 200000 | | Handler_read_retry | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 0 | +--------------------------+--------+ +-------------------------------------------------------+ | marker | +-------------------------------------------------------+ | ===== ratio=1 HINTED JOIN_ORDER(t1,t0): EXPLAIN ===== | +-------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 533.6416582, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 199018, "cost": 33.6793548, "filtered": 100, "index_condition": "t1.c0 is not null", "attached_condition": "t1.c6 = '' or t1.c1 = 0" } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg10y701270.t1.c0"], "loops": 200000, "rows": 1, "cost": 364.9404928, "filtered": 100 } } ] } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------+ | marker | +-------------------------------------------------------+ | ===== ratio=1 HINTED JOIN_ORDER(t1,t0): ANALYZE ===== | +-------------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.102981363 }, "query_block": { "select_id": 1, "cost": 533.6416582, "r_loops": 1, "r_total_time_ms": 308.2018123, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 199018, "r_index_rows": 200000, "r_rows": 200000, "cost": 33.6793548, "r_table_time_ms": 287.837203, "r_other_time_ms": 20.33146327, "r_engine_stats": { "pages_accessed": 600278 }, "filtered": 100, "r_total_filtered": 0.0025, "index_condition": "t1.c0 is not null", "r_icp_filtered": 100, "attached_condition": "t1.c6 = '' or t1.c1 = 0", "r_filtered": 0.0025 } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg10y701270.t1.c0"], "loops": 200000, "r_loops": 5, "rows": 1, "r_rows": 1, "cost": 364.9404928, "r_table_time_ms": 0.0174449, "r_other_time_ms": 0.010715579, "r_engine_stats": { "pages_accessed": 20 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } ] } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Handler_read_first | 0 | | Handler_read_key | 5 | | Handler_read_last | 1 | | Handler_read_next | 5 | | Handler_read_prev | 200000 | | Handler_read_retry | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 0 | +--------------------------+--------+ +------------------+ | marker | +------------------+ | ===== done ===== | +------------------+