+-----------------+ | mariadb_version | +-----------------+ | 12.3.2-MariaDB | +-----------------+ +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | optimizer_prune_level | 0 | +-----------------------+-------+ +----------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------------+---------+----------+----------+ | mdev_null_eq_impossible.t0 | analyze | status | OK | | mdev_null_eq_impossible.t1 | analyze | status | OK | +----------------------------+---------+----------+----------+ +----------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------------------+---------+----------+-----------------------------------------+ | mdev_null_eq_impossible.t0 | analyze | status | Engine-independent statistics collected | | mdev_null_eq_impossible.t0 | analyze | status | OK | +----------------------------+---------+----------+-----------------------------------------+ +----------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------------------+---------+----------+-----------------------------------------+ | mdev_null_eq_impossible.t1 | analyze | status | Engine-independent statistics collected | | mdev_null_eq_impossible.t1 | analyze | status | OK | +----------------------------+---------+----------+-----------------------------------------+ +---------+------------+ | t0_rows | t0_null_c0 | +---------+------------+ | 52000 | 2000 | +---------+------------+ +---------+------------+ | t1_rows | t1_null_c0 | +---------+------------+ | 52000 | 2000 | +---------+------------+ +------------------------+ | eq_join_should_be_zero | +------------------------+ | 0 | +------------------------+ +-------------------------+ | null_safe_join_has_rows | +-------------------------+ | 1 | +-------------------------+ +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | optimizer_prune_level | 0 | +-----------------------+-------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 590.6931915, "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t0.c3, t0.c0 desc", "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 1, "rows": 2000, "cost": 2.22527472, "filtered": 100, "index_condition": "t0.c0 is null", "attached_condition": "t0.c0 <=> NULL" } } } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 2000, "rows": 2000, "cost": 588.4679168, "filtered": 100, "attached_condition": "t1.c0 = t0.c0", "using_index": true } } ] } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.076346331 }, "query_block": { "select_id": 1, "cost": 590.6931915, "r_loops": 1, "r_total_time_ms": 1180.435477, "nested_loop": [ { "read_sorted_file": { "r_rows": 2000, "filesort": { "sort_key": "t0.c3, t0.c0 desc", "r_loops": 1, "r_total_time_ms": 4.613576941, "r_used_priority_queue": false, "r_output_rows": 2000, "r_buffer_size": "2047KiB", "r_sort_mode": "packed_sort_key,rowid", "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 1, "r_loops": 1, "rows": 2000, "r_index_rows": 2000, "r_rows": 2000, "cost": 2.22527472, "r_table_time_ms": 4.307729417, "r_other_time_ms": 2.46669686, "r_engine_stats": { "pages_accessed": 8018 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t0.c0 is null", "r_icp_filtered": 100, "attached_condition": "t0.c0 <=> NULL", "r_filtered": 100 } } } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 2000, "r_loops": 2000, "rows": 2000, "r_rows": 2000, "cost": 588.4679168, "r_table_time_ms": 1035.707774, "r_other_time_ms": 137.9358794, "r_engine_stats": { "pages_accessed": 8007 }, "filtered": 100, "r_total_filtered": 0, "attached_condition": "t1.c0 = t0.c0", "r_filtered": 0, "using_index": true } } ] } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 6723.141002, "filesort": { "sort_key": "t0.c3, t0.c0 desc", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 1, "rows": 2000, "cost": 0.29587072, "filtered": 100, "attached_condition": "t1.c0 is null", "using_index": true } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 2000, "rows": 2000, "cost": 3975.652646, "filtered": 100, "index_condition": "t0.c0 = t1.c0" } } ] } } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.093354606 }, "query_block": { "select_id": 1, "cost": 6723.141002, "r_loops": 1, "r_total_time_ms": 276.8652841, "filesort": { "sort_key": "t0.c3, t0.c0 desc", "r_loops": 1, "r_total_time_ms": 0.007313924, "r_limit": 2, "r_used_priority_queue": true, "r_output_rows": 0, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 1, "r_loops": 1, "rows": 2000, "r_rows": 2000, "cost": 0.29587072, "r_table_time_ms": 0.590368935, "r_other_time_ms": 0.13510435, "r_engine_stats": { "pages_accessed": 11 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 2000, "r_loops": 2000, "rows": 2000, "r_index_rows": 2000, "r_rows": 0, "cost": 3975.652646, "r_table_time_ms": 275.9456516, "r_other_time_ms": 0.172512509, "r_engine_stats": { "pages_accessed": 6007 }, "filtered": 100, "r_total_filtered": 0, "index_condition": "t0.c0 = t1.c0", "r_icp_filtered": 0, "r_filtered": 100 } } ] } } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 588.6917683, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 1, "rows": 2000, "cost": 0.29587072, "filtered": 100, "attached_condition": "t1.c0 is null", "using_index": true } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 2000, "rows": 2000, "cost": 588.3958976, "filtered": 100, "attached_condition": "t0.c0 = t1.c0", "using_index": true } } ] } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | optimizer_prune_level | 1 | +-----------------------+-------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 6723.141002, "filesort": { "sort_key": "t0.c3, t0.c0 desc", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 1, "rows": 2000, "cost": 0.29587072, "filtered": 100, "attached_condition": "t1.c0 is null", "using_index": true } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 2000, "rows": 2000, "cost": 3975.652646, "filtered": 100, "index_condition": "t0.c0 = t1.c0" } } ] } } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.080020191 }, "query_block": { "select_id": 1, "cost": 6723.141002, "r_loops": 1, "r_total_time_ms": 276.8174371, "filesort": { "sort_key": "t0.c3, t0.c0 desc", "r_loops": 1, "r_total_time_ms": 0.007476516, "r_limit": 2, "r_used_priority_queue": true, "r_output_rows": 0, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 1, "r_loops": 1, "rows": 2000, "r_rows": 2000, "cost": 0.29587072, "r_table_time_ms": 0.577300331, "r_other_time_ms": 0.136199566, "r_engine_stats": { "pages_accessed": 11 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i0", "i1"], "key": "i0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["const"], "loops": 2000, "r_loops": 2000, "rows": 2000, "r_index_rows": 2000, "r_rows": 0, "cost": 3975.652646, "r_table_time_ms": 275.9195801, "r_other_time_ms": 0.16371881, "r_engine_stats": { "pages_accessed": 6007 }, "filtered": 100, "r_total_filtered": 0, "index_condition": "t0.c0 = t1.c0", "r_icp_filtered": 0, "r_filtered": 100 } } ] } } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+