+-----------------+ | case_marker | +-----------------+ | ===== ENV ===== | +-----------------+ +-----------------+ | mariadb_version | +-----------------+ | 12.3.2-MariaDB | +-----------------+ +-----------------------+---------------------------------+ | optimizer_prune_level | optimizer_join_limit_pref_ratio | +-----------------------+---------------------------------+ | 2 | 0 | +-----------------------+---------------------------------+ +----------------------------+ | case_marker | +----------------------------+ | ===== ANALYZE TABLES ===== | +----------------------------+ +--------------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------+---------+----------+----------+ | mariadb_distinct_semijoin_scaleup.t0 | analyze | status | OK | | mariadb_distinct_semijoin_scaleup.t1 | analyze | status | OK | | mariadb_distinct_semijoin_scaleup.t2 | analyze | status | OK | | mariadb_distinct_semijoin_scaleup.t3 | analyze | status | OK | +--------------------------------------+---------+----------+----------+ +--------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------+---------+----------+-----------------------------------------+ | mariadb_distinct_semijoin_scaleup.t0 | analyze | status | Engine-independent statistics collected | | mariadb_distinct_semijoin_scaleup.t0 | analyze | status | OK | +--------------------------------------+---------+----------+-----------------------------------------+ +--------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------+---------+----------+-----------------------------------------+ | mariadb_distinct_semijoin_scaleup.t1 | analyze | status | Engine-independent statistics collected | | mariadb_distinct_semijoin_scaleup.t1 | analyze | status | OK | +--------------------------------------+---------+----------+-----------------------------------------+ +--------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------+---------+----------+-----------------------------------------+ | mariadb_distinct_semijoin_scaleup.t2 | analyze | status | Engine-independent statistics collected | | mariadb_distinct_semijoin_scaleup.t2 | analyze | status | OK | +--------------------------------------+---------+----------+-----------------------------------------+ +--------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------+---------+----------+-----------------------------------------+ | mariadb_distinct_semijoin_scaleup.t3 | analyze | status | Engine-independent statistics collected | | mariadb_distinct_semijoin_scaleup.t3 | analyze | status | OK | +--------------------------------------+---------+----------+-----------------------------------------+ +------------------------+ | case_marker | +------------------------+ | ===== DATA SHAPE ===== | +------------------------+ +-----+-----------+--------+--------+--------+ | tbl | row_count | ndv_c0 | ndv_c1 | ndv_c7 | +-----+-----------+--------+--------+--------+ | t0 | 320 | 8 | 8 | 320 | | t1 | 7000 | 3507 | 7000 | NULL | | t3 | 320 | 8 | 8 | 160 | +-----+-----------+--------+--------+--------+ +----------------------------------+ | case_marker | +----------------------------------+ | ===== JOIN FANOUT BY t0.c0 ===== | +----------------------------------+ +------+---------+---------+---------+---------------------+ | c0 | t0_rows | t1_rows | t3_rows | raw_inner_join_rows | +------+---------+---------+---------+---------------------+ | 0 | 40 | 0 | 40 | 0 | | 1 | 40 | 500 | 40 | 800000 | | 2 | 40 | 500 | 40 | 800000 | | 3 | 40 | 500 | 40 | 800000 | | 4 | 40 | 500 | 40 | 800000 | | 5 | 40 | 500 | 40 | 800000 | | 6 | 40 | 500 | 40 | 800000 | | 7 | 40 | 500 | 40 | 800000 | +------+---------+---------+---------+---------------------+ +----------------------------------------------+ | case_marker | +----------------------------------------------+ | ===== FULL DISTINCT RESULT CARDINALITY ===== | +----------------------------------------------+ +--------------------------+ | 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 | +----------------------------------------------------------------+ | ===== CASE A: default DISTINCT inner join / EXPLAIN JSON ===== | +----------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 4.659134086, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 320, "cost": 0.05358512, "filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null", "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "rows": 1, "cost": 0.32906144, "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": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 638.72, "rows": 40, "cost": 4.276487526, "filtered": 100, "using_index": true } } ] } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------------------------+ | case_marker | +-----------------------------------------------------------------+ | ===== CASE A1: default DISTINCT inner join / ANALYZE JSON ===== | +-----------------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.074914968 }, "query_block": { "select_id": 1, "cost": 4.659134086, "r_loops": 1, "r_total_time_ms": 3451.564536, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 320, "r_rows": 320, "cost": 0.05358512, "r_table_time_ms": 0.115287243, "r_other_time_ms": 0.090161216, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "r_loops": 320, "rows": 1, "r_rows": 437.5, "cost": 0.32906144, "r_table_time_ms": 37.14486522, "r_other_time_ms": 13.88210788, "r_engine_stats": { "pages_accessed": 800 }, "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": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 638.72, "r_loops": 140000, "rows": 40, "r_rows": 40, "cost": 4.276487526, "r_table_time_ms": 1749.277005, "r_other_time_ms": 1651.051763, "r_engine_stats": { "pages_accessed": 140000 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------+ | case_marker | +---------------------------------------------------------------------------+ | ===== CASE A_FORCED: forced bad JOIN_ORDER(t0,t1,t3) / EXPLAIN JSON ===== | +---------------------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 4.659134086, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 320, "cost": 0.05358512, "filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null", "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "rows": 1, "cost": 0.32906144, "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": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 638.72, "rows": 40, "cost": 4.276487526, "filtered": 100, "using_index": true } } ] } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------------------------------+ | case_marker | +----------------------------------------------------------------------------+ | ===== CASE A_FORCED1: forced bad JOIN_ORDER(t0,t1,t3) / ANALYZE JSON ===== | +----------------------------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.085040464 }, "query_block": { "select_id": 1, "cost": 4.659134086, "r_loops": 1, "r_total_time_ms": 3433.502719, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 320, "r_rows": 320, "cost": 0.05358512, "r_table_time_ms": 0.105551787, "r_other_time_ms": 0.08170001, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "r_loops": 320, "rows": 1, "r_rows": 437.5, "cost": 0.32906144, "r_table_time_ms": 36.92228791, "r_other_time_ms": 13.22902938, "r_engine_stats": { "pages_accessed": 800 }, "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": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 638.72, "r_loops": 140000, "rows": 40, "r_rows": 40, "cost": 4.276487526, "r_table_time_ms": 1739.361147, "r_other_time_ms": 1643.798962, "r_engine_stats": { "pages_accessed": 140000 }, "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": 32.11450275, "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": 320, "cost": 0.05358512, "filtered": 100, "attached_condition": "t3.c0 is not null and t3.c0 is not null", "using_index": true } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t3.c0"], "loops": 320, "rows": 40, "cost": 2.1429376, "filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t3.c0"], "loops": 12800, "rows": 1, "cost": 12.8749184, "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.090310106 }, "query_block": { "select_id": 1, "cost": 32.11450275, "r_loops": 1, "r_total_time_ms": 18.33670677, "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": 320, "r_rows": 320, "cost": 0.05358512, "r_table_time_ms": 0.093590274, "r_other_time_ms": 0.045099333, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c0 is not null and t3.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t3.c0"], "loops": 320, "r_loops": 320, "rows": 40, "r_rows": 40, "cost": 2.1429376, "r_table_time_ms": 4.035044377, "r_other_time_ms": 0.95433781, "r_engine_stats": { "pages_accessed": 320 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t3.c0"], "loops": 12800, "r_loops": 12800, "rows": 1, "r_rows": 0.875, "cost": 12.8749184, "r_table_time_ms": 9.680217986, "r_other_time_ms": 3.524018773, "r_engine_stats": { "pages_accessed": 25600 }, "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": 2.52558416, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 320, "cost": 0.05358512, "filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null", "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "rows": 1, "cost": 0.32906144, "filtered": 50.10020065, "using_index": true, "first_match": "t0" } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "rows": 40, "cost": 2.1429376, "filtered": 100, "using_index": true } } ] } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------+ | case_marker | +----------------------------------------------------+ | ===== CASE C1: EXISTS rewrite / ANALYZE JSON ===== | +----------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.108012597 }, "query_block": { "select_id": 1, "cost": 2.52558416, "r_loops": 1, "r_total_time_ms": 7.246822452, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 320, "r_rows": 320, "cost": 0.05358512, "r_table_time_ms": 0.093571092, "r_other_time_ms": 0.041758879, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "r_loops": 320, "rows": 1, "r_rows": 0.875, "cost": 0.32906144, "r_table_time_ms": 0.255908779, "r_other_time_ms": 0.0251717, "r_engine_stats": { "pages_accessed": 640 }, "filtered": 50.10020065, "r_total_filtered": 100, "r_filtered": 100, "using_index": true, "first_match": "t0" } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "r_loops": 280, "rows": 40, "r_rows": 40, "cost": 2.1429376, "r_table_time_ms": 3.514662395, "r_other_time_ms": 3.311847387, "r_engine_stats": { "pages_accessed": 280 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------+ | case_marker | +--------------------------------------------------------------+ | ===== CONTROL F: no ORDER/LIMIT default / ANALYZE JSON ===== | +--------------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.068122619 }, "query_block": { "select_id": 1, "cost": 4.659134086, "r_loops": 1, "r_total_time_ms": 3440.079281, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "index", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "18", "used_key_parts": ["c0", "c1"], "loops": 1, "r_loops": 1, "rows": 320, "r_rows": 320, "cost": 0.05358512, "r_table_time_ms": 0.102986844, "r_other_time_ms": 0.072677951, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c0 is not null and t3.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t3.c0"], "loops": 320, "r_loops": 320, "rows": 1, "r_rows": 437.5, "cost": 0.32906144, "r_table_time_ms": 36.94439776, "r_other_time_ms": 13.0268346, "r_engine_stats": { "pages_accessed": 800 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t3.c0"], "loops": 638.72, "r_loops": 140000, "rows": 40, "r_rows": 40, "cost": 4.276487526, "r_table_time_ms": 1743.984169, "r_other_time_ms": 1645.945781, "r_engine_stats": { "pages_accessed": 140000 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------------+ | case_marker | +--------------------------------------------------------------------+ | ===== CONTROL F_EXISTS: no ORDER/LIMIT EXISTS / ANALYZE JSON ===== | +--------------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.13998578 }, "query_block": { "select_id": 1, "cost": 2.52558416, "r_loops": 1, "r_total_time_ms": 7.171242498, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "index", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "18", "used_key_parts": ["c0", "c1"], "loops": 1, "r_loops": 1, "rows": 320, "r_rows": 320, "cost": 0.05358512, "r_table_time_ms": 0.09087918, "r_other_time_ms": 0.039240521, "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, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "r_loops": 320, "rows": 1, "r_rows": 0.875, "cost": 0.32906144, "r_table_time_ms": 0.26010056, "r_other_time_ms": 0.025355301, "r_engine_stats": { "pages_accessed": 640 }, "filtered": 50.10020065, "r_total_filtered": 100, "r_filtered": 100, "using_index": true, "first_match": "t0" } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i6", "i7"], "key": "i7", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "r_loops": 280, "rows": 40, "r_rows": 40, "cost": 2.1429376, "r_table_time_ms": 3.478208783, "r_other_time_ms": 3.273585161, "r_engine_stats": { "pages_accessed": 280 }, "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": 4.659134086, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 320, "cost": 0.05358512, "filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null", "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "rows": 1, "cost": 0.32906144, "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": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 638.72, "rows": 40, "cost": 4.276487526, "filtered": 100, "using_index": true } } ] } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.07803711 }, "query_block": { "select_id": 1, "cost": 4.659134086, "r_loops": 1, "r_total_time_ms": 3447.258619, "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "range", "possible_keys": ["i0", "i1"], "key": "i1", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 320, "r_rows": 320, "cost": 0.05358512, "r_table_time_ms": 0.097546388, "r_other_time_ms": 0.079234632, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t0.c0 is not null and t0.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 320, "r_loops": 320, "rows": 1, "r_rows": 437.5, "cost": 0.32906144, "r_table_time_ms": 37.10263775, "r_other_time_ms": 13.81429949, "r_engine_stats": { "pages_accessed": 800 }, "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": ["mariadb_distinct_semijoin_scaleup.t0.c0"], "loops": 638.72, "r_loops": 140000, "rows": 40, "r_rows": 40, "cost": 4.276487526, "r_table_time_ms": 1747.50172, "r_other_time_ms": 1648.659241, "r_engine_stats": { "pages_accessed": 140000 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------+ | case_marker | +------------------+ | ===== DONE ===== | +------------------+