+-----------------+ | case_marker | +-----------------+ | ===== ENV ===== | +-----------------+ +-----------------+ | mariadb_version | +-----------------+ | 12.3.2-MariaDB | +-----------------+ +-----------------------+---------------------------------+ | optimizer_prune_level | optimizer_join_limit_pref_ratio | +-----------------------+---------------------------------+ | 0 | 0 | +-----------------------+---------------------------------+ +----------------------------+ | case_marker | +----------------------------+ | ===== ANALYZE TABLES ===== | +----------------------------+ +-----------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+---------+----------+----------+ | mariadb1232qpg17hw700180.t0 | analyze | status | OK | | mariadb1232qpg17hw700180.t1 | analyze | status | OK | | mariadb1232qpg17hw700180.t2 | analyze | status | OK | | mariadb1232qpg17hw700180.t3 | analyze | status | OK | +-----------------------------+---------+----------+----------+ +-----------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+---------+----------+-----------------------------------------+ | mariadb1232qpg17hw700180.t0 | analyze | status | Engine-independent statistics collected | | mariadb1232qpg17hw700180.t0 | analyze | status | OK | +-----------------------------+---------+----------+-----------------------------------------+ +-----------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+---------+----------+-----------------------------------------+ | mariadb1232qpg17hw700180.t1 | analyze | status | Engine-independent statistics collected | | mariadb1232qpg17hw700180.t1 | analyze | status | OK | +-----------------------------+---------+----------+-----------------------------------------+ +-----------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+---------+----------+-----------------------------------------+ | mariadb1232qpg17hw700180.t2 | analyze | status | Engine-independent statistics collected | | mariadb1232qpg17hw700180.t2 | analyze | status | OK | +-----------------------------+---------+----------+-----------------------------------------+ +-----------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+---------+----------+-----------------------------------------+ | mariadb1232qpg17hw700180.t3 | analyze | status | Engine-independent statistics collected | | mariadb1232qpg17hw700180.t3 | analyze | status | OK | +-----------------------------+---------+----------+-----------------------------------------+ +------------------------+ | case_marker | +------------------------+ | ===== DATA SHAPE ===== | +------------------------+ +-----+-----------+--------+--------+--------+ | tbl | row_count | ndv_c0 | ndv_c1 | ndv_c7 | +-----+-----------+--------+--------+--------+ | t0 | 90 | 8 | 8 | 90 | | t1 | 203 | 32 | 203 | NULL | | t3 | 117 | 8 | 8 | 40 | +-----+-----------+--------+--------+--------+ +----------------------------------+ | case_marker | +----------------------------------+ | ===== JOIN FANOUT BY t0.c0 ===== | +----------------------------------+ +------+---------+---------+---------+---------------------+ | c0 | t0_rows | t1_rows | t3_rows | raw_inner_join_rows | +------+---------+---------+---------+---------------------+ | 0 | 12 | 0 | 15 | 0 | | 1 | 12 | 48 | 15 | 8640 | | 2 | 11 | 19 | 15 | 3135 | | 3 | 11 | 15 | 15 | 2475 | | 4 | 11 | 11 | 15 | 1815 | | 5 | 11 | 14 | 14 | 2156 | | 6 | 11 | 5 | 14 | 770 | | 7 | 11 | 8 | 14 | 1232 | +------+---------+---------+---------+---------------------+ +----------------------------------------------------------------------+ | case_marker | +----------------------------------------------------------------------+ | ===== FULL DISTINCT RESULT CARDINALITY, WITHOUT ORDER BY/LIMIT ===== | +----------------------------------------------------------------------+ +--------------------------+ | inner_join_distinct_rows | +--------------------------+ | 7 | +--------------------------+ +------------------------------+ | exists_rewrite_distinct_rows | +------------------------------+ | 7 | +------------------------------+ +-------------------------------------------------------------+ | case_marker | +-------------------------------------------------------------+ | ===== RESULT EQUIVALENCE: default vs hinted vs EXISTS ===== | +-------------------------------------------------------------+ +--------------+-----+ | check_name | cnt | +--------------+-----+ | default_rows | 7 | | hinted_rows | 7 | | exists_rows | 7 | +--------------+-----+ +----------------------+-----+ | check_name | cnt | +----------------------+-----+ | default_minus_hinted | 0 | | hinted_minus_default | 0 | | default_minus_exists | 0 | | exists_minus_default | 0 | +----------------------+-----+ +-----------------------------------+ | case_marker | +-----------------------------------+ | ===== DEFAULT RESULT SAMPLE ===== | +-----------------------------------+ +--------+--------+-------+ | ref0 | t3_c1 | t0_c0 | +--------+--------+-------+ | 999999 | 999999 | 1 | | 999998 | 999998 | 2 | | 999997 | 999997 | 3 | | 999996 | 999996 | 4 | | 999995 | 999995 | 5 | | 999994 | 999994 | 6 | | 999993 | 999993 | 7 | +--------+--------+-------+ +----------------------------------------------------------------+ | case_marker | +----------------------------------------------------------------+ | ===== CASE A: default DISTINCT inner join / EXPLAIN JSON ===== | +----------------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 1.837676836, "filesort": { "sort_key": "t0.c7", "temporary_table": { "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": 90, "cost": 0.0255248, "filtered": 100, "index_condition": "t0.c0 is not null and t0.c0 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "rows": 6, "cost": 0.155424044, "filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 570.942, "rows": 14, "cost": 1.656727992, "filtered": 100, "using_index": true } } ] } } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------------------------+ | case_marker | +-----------------------------------------------------------------+ | ===== CASE A1: default DISTINCT inner join / ANALYZE JSON ===== | +-----------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.072033061 }, "query_block": { "select_id": 1, "cost": 1.837676836, "r_loops": 1, "r_total_time_ms": 13.59251504, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.004984649, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "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": 90, "r_index_rows": 90, "r_rows": 90, "cost": 0.0255248, "r_table_time_ms": 0.083735157, "r_other_time_ms": 0.011190568, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is not null and t0.c0 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "r_loops": 90, "rows": 6, "r_rows": 15.2, "cost": 0.155424044, "r_table_time_ms": 0.404432494, "r_other_time_ms": 0.127433271, "r_engine_stats": { "pages_accessed": 90 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 570.942, "r_loops": 1368, "rows": 14, "r_rows": 14.78289474, "cost": 1.656727992, "r_table_time_ms": 6.805612074, "r_other_time_ms": 6.145282676, "r_engine_stats": { "pages_accessed": 1368 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------------------------+ | case_marker | +-----------------------------------------------------------------+ | ===== CASE A2: default DISTINCT inner join / ANALYZE JSON ===== | +-----------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.077411403 }, "query_block": { "select_id": 1, "cost": 1.837676836, "r_loops": 1, "r_total_time_ms": 13.65566946, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.004854027, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "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": 90, "r_index_rows": 90, "r_rows": 90, "cost": 0.0255248, "r_table_time_ms": 0.082480088, "r_other_time_ms": 0.010630629, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is not null and t0.c0 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "r_loops": 90, "rows": 6, "r_rows": 15.2, "cost": 0.155424044, "r_table_time_ms": 0.403857026, "r_other_time_ms": 0.125875854, "r_engine_stats": { "pages_accessed": 90 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 570.942, "r_loops": 1368, "rows": 14, "r_rows": 14.78289474, "cost": 1.656727992, "r_table_time_ms": 6.827936577, "r_other_time_ms": 6.191273528, "r_engine_stats": { "pages_accessed": 1368 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------------------------+ | case_marker | +-----------------------------------------------------------------+ | ===== CASE A3: default DISTINCT inner join / ANALYZE JSON ===== | +-----------------------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.075463033 }, "query_block": { "select_id": 1, "cost": 1.837676836, "r_loops": 1, "r_total_time_ms": 13.62763047, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.004117793, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "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": 90, "r_index_rows": 90, "r_rows": 90, "cost": 0.0255248, "r_table_time_ms": 0.08384477, "r_other_time_ms": 0.010258858, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is not null and t0.c0 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "r_loops": 90, "rows": 6, "r_rows": 15.2, "cost": 0.155424044, "r_table_time_ms": 0.403847892, "r_other_time_ms": 0.12539447, "r_engine_stats": { "pages_accessed": 90 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 570.942, "r_loops": 1368, "rows": 14, "r_rows": 14.78289474, "cost": 1.656727992, "r_table_time_ms": 6.814525433, "r_other_time_ms": 6.177294224, "r_engine_stats": { "pages_accessed": 1368 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------+ | case_marker | +---------------------------------------------------------+ | ===== CASE B: JOIN_ORDER(t3,t0,t1) / EXPLAIN JSON ===== | +---------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 8.829950337, "filesort": { "sort_key": "t0.c7", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "range", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 117, "cost": 0.023808065, "filtered": 100, "attached_condition": "t3.c0 is not null", "using_index": true } }, { "block-nl-join": { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "loops": 117, "rows": 90, "cost": 1.010525102, "filtered": 12.5 }, "buffer_type": "flat", "buffer_size": "2KiB", "join_type": "BNL", "attached_condition": "t0.c0 = t3.c0" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t3.c0"], "loops": 1316.25, "rows": 6, "cost": 2.261915044, "filtered": 100, "using_index": true, "distinct": true } } ] } } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------------+ | case_marker | +----------------------------------------------------------+ | ===== CASE B1: JOIN_ORDER(t3,t0,t1) / ANALYZE JSON ===== | +----------------------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.070406222 }, "query_block": { "select_id": 1, "cost": 8.829950337, "r_loops": 1, "r_total_time_ms": 2.050706595, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.004622013, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "range", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 117, "r_rows": 117, "cost": 0.023808065, "r_table_time_ms": 0.035607766, "r_other_time_ms": 0.01374455, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c0 is not null", "r_filtered": 100, "using_index": true } }, { "block-nl-join": { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "loops": 117, "r_loops": 1, "rows": 90, "r_rows": 90, "cost": 1.010525102, "r_table_time_ms": 0.073830713, "r_other_time_ms": 0.004896045, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 12.5, "r_total_filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "2KiB", "join_type": "BNL", "attached_condition": "t0.c0 = t3.c0", "r_loops": 117, "r_filtered": 12.50712251, "r_unpack_time_ms": 0.391204038, "r_other_time_ms": 0.442876495, "r_effective_rows": 90 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t3.c0"], "loops": 1316.25, "r_loops": 1317, "rows": 6, "r_rows": 0.86332574, "cost": 2.261915044, "r_table_time_ms": 0.701363918, "r_other_time_ms": 0.374439652, "r_engine_stats": { "pages_accessed": 1317 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true, "distinct": true } } ] } } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------------+ | case_marker | +----------------------------------------------------------+ | ===== CASE B2: JOIN_ORDER(t3,t0,t1) / ANALYZE JSON ===== | +----------------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.072597568 }, "query_block": { "select_id": 1, "cost": 8.829950337, "r_loops": 1, "r_total_time_ms": 2.045657091, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.004124187, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "range", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 117, "r_rows": 117, "cost": 0.023808065, "r_table_time_ms": 0.035248783, "r_other_time_ms": 0.013367299, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c0 is not null", "r_filtered": 100, "using_index": true } }, { "block-nl-join": { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "loops": 117, "r_loops": 1, "rows": 90, "r_rows": 90, "cost": 1.010525102, "r_table_time_ms": 0.07349, "r_other_time_ms": 0.004747154, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 12.5, "r_total_filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "2KiB", "join_type": "BNL", "attached_condition": "t0.c0 = t3.c0", "r_loops": 117, "r_filtered": 12.50712251, "r_unpack_time_ms": 0.379716601, "r_other_time_ms": 0.443435521, "r_effective_rows": 90 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t3.c0"], "loops": 1316.25, "r_loops": 1317, "rows": 6, "r_rows": 0.86332574, "cost": 2.261915044, "r_table_time_ms": 0.702583362, "r_other_time_ms": 0.380957055, "r_engine_stats": { "pages_accessed": 1317 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true, "distinct": true } } ] } } } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------------+ | case_marker | +----------------------------------------------------------+ | ===== CASE B3: JOIN_ORDER(t3,t0,t1) / ANALYZE JSON ===== | +----------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.072213922 }, "query_block": { "select_id": 1, "cost": 8.829950337, "r_loops": 1, "r_total_time_ms": 2.047718728, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.003826405, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "range", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 117, "r_rows": 117, "cost": 0.023808065, "r_table_time_ms": 0.035144651, "r_other_time_ms": 0.013197399, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c0 is not null", "r_filtered": 100, "using_index": true } }, { "block-nl-join": { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "loops": 117, "r_loops": 1, "rows": 90, "r_rows": 90, "cost": 1.010525102, "r_table_time_ms": 0.075893264, "r_other_time_ms": 0.004700569, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 12.5, "r_total_filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "2KiB", "join_type": "BNL", "attached_condition": "t0.c0 = t3.c0", "r_loops": 117, "r_filtered": 12.50712251, "r_unpack_time_ms": 0.379364927, "r_other_time_ms": 0.448875977, "r_effective_rows": 90 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t3.c0"], "loops": 1316.25, "r_loops": 1317, "rows": 6, "r_rows": 0.86332574, "cost": 2.261915044, "r_table_time_ms": 0.705990498, "r_other_time_ms": 0.3728731, "r_engine_stats": { "pages_accessed": 1317 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true, "distinct": true } } ] } } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------+ | case_marker | +---------------------------------------------------+ | ===== CASE C: EXISTS rewrite / EXPLAIN JSON ===== | +---------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 0.399522947, "filesort": { "sort_key": "t0.c7", "temporary_table": { "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": 90, "cost": 0.0255248, "filtered": 100, "index_condition": "t0.c0 is not null" } }, { "table": { "table_name": "", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", "used_key_parts": ["c0"], "ref": ["func"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 203, "cost": 0.036422975, "filtered": 100, "using_index": true } } ] } } } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "rows": 14, "cost": 0.2618471, "filtered": 100, "using_index": true } } ] } } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------+ | case_marker | +----------------------------------------------------+ | ===== CASE C1: EXISTS rewrite / ANALYZE JSON ===== | +----------------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.097123465 }, "query_block": { "select_id": 1, "cost": 0.399522947, "r_loops": 1, "r_total_time_ms": 0.971766268, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.004011834, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "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": 90, "r_index_rows": 90, "r_rows": 90, "cost": 0.0255248, "r_table_time_ms": 0.080308839, "r_other_time_ms": 0.021276786, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", "used_key_parts": ["c0"], "ref": ["func"], "r_loops": 90, "r_table_loops": 211, "rows": 1, "r_rows": 0.866666667, "r_table_time_ms": 0.030769267, "r_other_time_ms": 0.006053375, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 203, "r_rows": 203, "cost": 0.036422975, "r_table_time_ms": 0.052085332, "r_other_time_ms": 0.016662995, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "r_loops": 78, "rows": 14, "r_rows": 14.57692308, "cost": 0.2618471, "r_table_time_ms": 0.388629047, "r_other_time_ms": 0.36350576, "r_engine_stats": { "pages_accessed": 78 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------+ | case_marker | +----------------------------------------------------+ | ===== CASE C2: EXISTS rewrite / ANALYZE JSON ===== | +----------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.101581971 }, "query_block": { "select_id": 1, "cost": 0.399522947, "r_loops": 1, "r_total_time_ms": 0.967535208, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.003834626, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "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": 90, "r_index_rows": 90, "r_rows": 90, "cost": 0.0255248, "r_table_time_ms": 0.078083696, "r_other_time_ms": 0.021325199, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", "used_key_parts": ["c0"], "ref": ["func"], "r_loops": 90, "r_table_loops": 211, "rows": 1, "r_rows": 0.866666667, "r_table_time_ms": 0.037530559, "r_other_time_ms": 0.006137412, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 203, "r_rows": 203, "cost": 0.036422975, "r_table_time_ms": 0.052538399, "r_other_time_ms": 0.016473912, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "r_loops": 78, "rows": 14, "r_rows": 14.57692308, "cost": 0.2618471, "r_table_time_ms": 0.385331525, "r_other_time_ms": 0.357860693, "r_engine_stats": { "pages_accessed": 78 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------+ | case_marker | +----------------------------------------------------+ | ===== CASE C3: EXISTS rewrite / ANALYZE JSON ===== | +----------------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.097858785 }, "query_block": { "select_id": 1, "cost": 0.399522947, "r_loops": 1, "r_total_time_ms": 0.964977573, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.003766118, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "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": 90, "r_index_rows": 90, "r_rows": 90, "cost": 0.0255248, "r_table_time_ms": 0.078384218, "r_other_time_ms": 0.02130693, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", "used_key_parts": ["c0"], "ref": ["func"], "r_loops": 90, "r_table_loops": 211, "rows": 1, "r_rows": 0.866666667, "r_table_time_ms": 0.030747345, "r_other_time_ms": 0.006068903, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 203, "r_rows": 203, "cost": 0.036422975, "r_table_time_ms": 0.052097207, "r_other_time_ms": 0.016392616, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "r_loops": 78, "rows": 14, "r_rows": 14.57692308, "cost": 0.2618471, "r_table_time_ms": 0.390528092, "r_other_time_ms": 0.357381137, "r_engine_stats": { "pages_accessed": 78 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------+ | case_marker | +---------------------------------------------------+ | ===== CONTROL D: no DISTINCT / EXPLAIN JSON ===== | +---------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 1.837676836, "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t0.c7", "table": { "table_name": "t0", "access_type": "ALL", "possible_keys": ["i0", "i1"], "loops": 1, "rows": 90, "cost": 0.0255248, "filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null" } } } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "rows": 6, "cost": 0.155424044, "filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 570.942, "rows": 14, "cost": 1.656727992, "filtered": 100, "using_index": true } } ] } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------+ | case_marker | +----------------------------------------------------+ | ===== CONTROL D1: no DISTINCT / ANALYZE JSON ===== | +----------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.04622652 }, "query_block": { "select_id": 1, "cost": 1.837676836, "r_loops": 1, "r_total_time_ms": 0.067502393, "nested_loop": [ { "read_sorted_file": { "r_rows": 1, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.05294762, "r_used_priority_queue": false, "r_output_rows": 90, "r_buffer_size": "18KiB", "r_sort_mode": "sort_key,packed_addon_fields", "table": { "table_name": "t0", "access_type": "ALL", "possible_keys": ["i0", "i1"], "loops": 1, "r_loops": 1, "rows": 90, "r_rows": 90, "cost": 0.0255248, "r_table_time_ms": 0.033177099, "r_other_time_ms": 0.015292833, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null", "r_filtered": 100 } } } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "r_loops": 1, "rows": 6, "r_rows": 1, "cost": 0.155424044, "r_table_time_ms": 0.002279036, "r_other_time_ms": 8.906052e-4, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 570.942, "r_loops": 1, "rows": 14, "r_rows": 8, "cost": 1.656727992, "r_table_time_ms": 0.003343195, "r_other_time_ms": 0.003732321, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------------------------------------------+ | case_marker | +------------------------------------------------------------------------------+ | ===== CONTROL E: optimizer_join_limit_pref_ratio=100 / EXPLAIN+ANALYZE ===== | +------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 1.837676836, "filesort": { "sort_key": "t0.c7", "temporary_table": { "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": 90, "cost": 0.0255248, "filtered": 100, "index_condition": "t0.c0 is not null and t0.c0 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "rows": 6, "cost": 0.155424044, "filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 570.942, "rows": 14, "cost": 1.656727992, "filtered": 100, "using_index": true } } ] } } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.065674597 }, "query_block": { "select_id": 1, "cost": 1.837676836, "r_loops": 1, "r_total_time_ms": 13.59658534, "filesort": { "sort_key": "t0.c7", "r_loops": 1, "r_total_time_ms": 0.004692348, "r_limit": 8, "r_used_priority_queue": true, "r_output_rows": 7, "r_sort_mode": "sort_key,rowid", "temporary_table": { "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": 90, "r_index_rows": 90, "r_rows": 90, "cost": 0.0255248, "r_table_time_ms": 0.08540036, "r_other_time_ms": 0.010718319, "r_engine_stats": { "pages_accessed": 93 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is not null and t0.c0 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 90, "r_loops": 90, "rows": 6, "r_rows": 15.2, "cost": 0.155424044, "r_table_time_ms": 0.402701523, "r_other_time_ms": 0.125320482, "r_engine_stats": { "pages_accessed": 90 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb1232qpg17hw700180.t0.c0"], "loops": 570.942, "r_loops": 1368, "rows": 14, "r_rows": 14.78289474, "cost": 1.656727992, "r_table_time_ms": 6.809311511, "r_other_time_ms": 6.150101079, "r_engine_stats": { "pages_accessed": 1368 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------+ | case_marker | +------------------+ | ===== DONE ===== | +------------------+