+-----------------------------+ | section | +-----------------------------+ | initial optimizer variables | +-----------------------------+ +----------------+-----------------------+---------------------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | version | optimizer_prune_level | optimizer_join_limit_pref_ratio | join_cache_level | optimizer_switch | +----------------+-----------------------+---------------------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 12.3.2-MariaDB | 0 | 0 | 0 | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,duplicateweedout=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on,cset_narrowing=on,sargable_casefold=on,reorder_outer_joins=off | +----------------+-----------------------+---------------------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+----------+ | mariadb_order_limit_equality_key_costing.t0 | analyze | status | OK | | mariadb_order_limit_equality_key_costing.t1 | analyze | status | OK | +---------------------------------------------+---------+----------+----------+ +---------------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+-----------------------------------------+ | mariadb_order_limit_equality_key_costing.t0 | analyze | status | Engine-independent statistics collected | | mariadb_order_limit_equality_key_costing.t0 | analyze | status | OK | +---------------------------------------------+---------+----------+-----------------------------------------+ +---------------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+-----------------------------------------+ | mariadb_order_limit_equality_key_costing.t1 | analyze | status | Engine-independent statistics collected | | mariadb_order_limit_equality_key_costing.t1 | analyze | status | OK | +---------------------------------------------+---------+----------+-----------------------------------------+ +------------+ | section | +------------+ | data shape | +------------+ +---------+---------+--------+-----------------+--------+-----------------+ | t0_rows | t1_rows | t0_ndv | t0_rows_per_key | t1_ndv | t1_rows_per_key | +---------+---------+--------+-----------------+--------+-----------------+ | 48000 | 72000 | 4000 | 12.0000 | 4000 | 18.0000 | +---------+---------+--------+-----------------+--------+-----------------+ +------------------+ | section | +------------------+ | join cardinality | +------------------+ +-------------------------+ | join_rows_before_filter | +-------------------------+ | 864000 | +-------------------------+ +------------------------+ | join_rows_after_filter | +------------------------+ | 859680 | +------------------------+ +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | t0 | 1 | idx_t0_c0 | 1 | c0 | A | 4000 | NULL | NULL | YES | BTREE | | | NO | | t0 | 1 | idx_t0_c0_c1 | 1 | c0 | A | 4000 | NULL | NULL | YES | BTREE | | | NO | | t0 | 1 | idx_t0_c0_c1 | 2 | c1 | A | 4000 | NULL | NULL | YES | BTREE | | | NO | +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | t1 | 1 | idx_t1_c0 | 1 | c0 | A | 4000 | NULL | NULL | YES | BTREE | | | NO | | t1 | 1 | idx_t1_c1 | 1 | c1 | A | 72000 | NULL | NULL | YES | BTREE | | | NO | | t1 | 1 | idx_t1_c3_c0_c2 | 1 | c3 | A | 72000 | NULL | NULL | YES | BTREE | | | NO | | t1 | 1 | idx_t1_c3_c0_c2 | 2 | c0 | A | 72000 | NULL | NULL | YES | BTREE | | | NO | | t1 | 1 | idx_t1_c3_c0_c2 | 3 | c2 | A | 72000 | NULL | NULL | YES | BTREE | | | NO | +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ +-------------------------------------------------+ | section | +-------------------------------------------------+ | 1A BASE original: ORDER BY t0.c0, t1.c3 LIMIT 3 | +-------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 901.9646724, "filesort": { "sort_key": "t0.c0, t1.c3 desc", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "loops": 1, "rows": 48000, "cost": 8.02633, "filtered": 99.5, "attached_condition": "t0.c4 is not null and t0.c0 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t0.c0"], "loops": 47760, "rows": 18, "cost": 893.9383424, "filtered": 100 } } ] } } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.076578345 }, "query_block": { "select_id": 1, "cost": 901.9646724, "r_loops": 1, "r_total_time_ms": 2615.21561, "filesort": { "sort_key": "t0.c0, t1.c3 desc", "r_loops": 1, "r_total_time_ms": 1031.598935, "r_limit": 3, "r_used_priority_queue": true, "r_output_rows": 4, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "loops": 1, "r_loops": 1, "rows": 48000, "r_rows": 48000, "cost": 8.02633, "r_table_time_ms": 15.96316539, "r_other_time_ms": 6.35085993, "r_engine_stats": { "pages_accessed": 197 }, "filtered": 99.5, "r_total_filtered": 99.5, "attached_condition": "t0.c4 is not null and t0.c0 is not null", "r_filtered": 99.5 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t0.c0"], "loops": 47760, "r_loops": 47760, "rows": 18, "r_rows": 18, "cost": 893.9383424, "r_table_time_ms": 1135.931462, "r_other_time_ms": 424.3695717, "r_engine_stats": { "pages_accessed": 1817028 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } ] } } } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------+ | section | +--------------------------------------------------------+ | 2A HINTED JOIN_ORDER(t1,t0): original ORDER BY LIMIT 3 | +--------------------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 928.9865715, "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t0.c0, t1.c3 desc", "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c3_c0_c2", "key_length": "277", "used_key_parts": ["c3", "c0", "c2"], "loops": 1, "rows": 72000, "cost": 10.89791872, "filtered": 100, "attached_condition": "t1.c0 is not null", "using_index": true } } } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "key": "idx_t0_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t1.c0"], "loops": 72000, "rows": 12, "cost": 918.0886528, "filtered": 99.5, "attached_condition": "t0.c4 is not null" } } ] } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.056500907 }, "query_block": { "select_id": 1, "cost": 928.9865715, "r_loops": 1, "r_total_time_ms": 65.2537388, "nested_loop": [ { "read_sorted_file": { "r_rows": 19, "filesort": { "sort_key": "t0.c0, t1.c3 desc", "r_loops": 1, "r_total_time_ms": 63.87112128, "r_used_priority_queue": false, "r_output_rows": 72000, "r_sort_passes": 1, "r_buffer_size": "2047KiB", "r_sort_mode": "packed_sort_key,rowid", "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c3_c0_c2", "key_length": "277", "used_key_parts": ["c3", "c0", "c2"], "loops": 1, "r_loops": 1, "rows": 72000, "r_rows": 72000, "cost": 10.89791872, "r_table_time_ms": 21.4885357, "r_other_time_ms": 43.21379974, "r_engine_stats": { "pages_accessed": 2075 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is not null", "r_filtered": 100, "using_index": true } } } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "key": "idx_t0_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t1.c0"], "loops": 72000, "r_loops": 19, "rows": 12, "r_rows": 11.52631579, "cost": 918.0886528, "r_table_time_ms": 0.278777693, "r_other_time_ms": 0.109224734, "r_engine_stats": { "pages_accessed": 476 }, "filtered": 99.5, "r_total_filtered": 1.369863014, "attached_condition": "t0.c4 is not null", "r_filtered": 1.369863014 } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------------------+ | section | +------------------------------------------------------+ | 2B STRAIGHT_JOIN t1 -> t0: original ORDER BY LIMIT 3 | +------------------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 928.9865715, "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t0.c0, t1.c3 desc", "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c3_c0_c2", "key_length": "277", "used_key_parts": ["c3", "c0", "c2"], "loops": 1, "rows": 72000, "cost": 10.89791872, "filtered": 100, "attached_condition": "t1.c0 is not null", "using_index": true } } } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "key": "idx_t0_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t1.c0"], "loops": 72000, "rows": 12, "cost": 918.0886528, "filtered": 99.5, "attached_condition": "t0.c4 is not null" } } ] } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.069047022 }, "query_block": { "select_id": 1, "cost": 928.9865715, "r_loops": 1, "r_total_time_ms": 62.18989892, "nested_loop": [ { "read_sorted_file": { "r_rows": 19, "filesort": { "sort_key": "t0.c0, t1.c3 desc", "r_loops": 1, "r_total_time_ms": 60.80666575, "r_used_priority_queue": false, "r_output_rows": 72000, "r_sort_passes": 1, "r_buffer_size": "2047KiB", "r_sort_mode": "packed_sort_key,rowid", "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c3_c0_c2", "key_length": "277", "used_key_parts": ["c3", "c0", "c2"], "loops": 1, "r_loops": 1, "rows": 72000, "r_rows": 72000, "cost": 10.89791872, "r_table_time_ms": 20.49219878, "r_other_time_ms": 41.14656991, "r_engine_stats": { "pages_accessed": 2075 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is not null", "r_filtered": 100, "using_index": true } } } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "key": "idx_t0_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t1.c0"], "loops": 72000, "r_loops": 19, "rows": 12, "r_rows": 11.52631579, "cost": 918.0886528, "r_table_time_ms": 0.27677543, "r_other_time_ms": 0.111578672, "r_engine_stats": { "pages_accessed": 476 }, "filtered": 99.5, "r_total_filtered": 1.369863014, "attached_condition": "t0.c4 is not null", "r_filtered": 1.369863014 } } ] } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------+ | section | +--------------------------------------------------+ | 3A BASE rewritten: ORDER BY t1.c0, t1.c3 LIMIT 3 | +--------------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 928.9865715, "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t1.c0, t1.c3 desc", "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c3_c0_c2", "key_length": "277", "used_key_parts": ["c3", "c0", "c2"], "loops": 1, "rows": 72000, "cost": 10.89791872, "filtered": 100, "attached_condition": "t1.c0 is not null", "using_index": true } } } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "key": "idx_t0_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t1.c0"], "loops": 72000, "rows": 12, "cost": 918.0886528, "filtered": 99.5, "attached_condition": "t0.c4 is not null" } } ] } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.077358423 }, "query_block": { "select_id": 1, "cost": 928.9865715, "r_loops": 1, "r_total_time_ms": 150.394886, "nested_loop": [ { "read_sorted_file": { "r_rows": 19, "filesort": { "sort_key": "t1.c0, t1.c3 desc", "r_loops": 1, "r_total_time_ms": 148.9483532, "r_used_priority_queue": false, "r_output_rows": 72000, "r_sort_passes": 1, "r_buffer_size": "2047KiB", "r_sort_mode": "packed_sort_key,rowid", "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c3_c0_c2", "key_length": "277", "used_key_parts": ["c3", "c0", "c2"], "loops": 1, "r_loops": 1, "rows": 72000, "r_rows": 72000, "cost": 10.89791872, "r_table_time_ms": 21.1344667, "r_other_time_ms": 128.6905224, "r_engine_stats": { "pages_accessed": 2075 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is not null", "r_filtered": 100, "using_index": true } } } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "key": "idx_t0_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t1.c0"], "loops": 72000, "r_loops": 19, "rows": 12, "r_rows": 11.52631579, "cost": 918.0886528, "r_table_time_ms": 0.283002359, "r_other_time_ms": 0.119434266, "r_engine_stats": { "pages_accessed": 476 }, "filtered": 99.5, "r_total_filtered": 1.369863014, "attached_condition": "t0.c4 is not null", "r_filtered": 1.369863014 } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------+ | section | +----------------------------------+ | 3B first rows, original ORDER BY | +----------------------------------+ +-------+-------+---------------------+--------+ | t0_c0 | t1_c0 | c3 | ref0 | +-------+-------+---------------------+--------+ | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | +-------+-------+---------------------+--------+ +-----------------------------------+ | section | +-----------------------------------+ | 3C first rows, rewritten ORDER BY | +-----------------------------------+ +-------+-------+---------------------+--------+ | t0_c0 | t1_c0 | c3 | ref0 | +-------+-------+---------------------+--------+ | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | | 1 | 1 | gaussian_1_3_940097 | 1153.7 | +-------+-------+---------------------+--------+ +------------------------------------------------+ | section | +------------------------------------------------+ | 4A original, optimizer_join_limit_pref_ratio=1 | +------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 901.9646724, "filesort": { "sort_key": "t0.c0, t1.c3 desc", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "loops": 1, "rows": 48000, "cost": 8.02633, "filtered": 99.5, "attached_condition": "t0.c4 is not null and t0.c0 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t0.c0"], "loops": 47760, "rows": 18, "cost": 893.9383424, "filtered": 100 } } ] } } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------+ | section | +--------------------------------------------------+ | 4B original, optimizer_join_limit_pref_ratio=100 | +--------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 901.9646724, "filesort": { "sort_key": "t0.c0, t1.c3 desc", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "loops": 1, "rows": 48000, "cost": 8.02633, "filtered": 99.5, "attached_condition": "t0.c4 is not null and t0.c0 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t0.c0"], "loops": 47760, "rows": 18, "cost": 893.9383424, "filtered": 100 } } ] } } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------+ | section | +---------------------------+ | 5A optimizer_switch check | +---------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | optimizer_switch | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,duplicateweedout=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on,cset_narrowing=on,sargable_casefold=on,reorder_outer_joins=off | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------+ | section | +---------------------------------------------------------------------+ | 5B original ORDER BY with orderby_uses_equalities currently enabled | +---------------------------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 901.9646724, "filesort": { "sort_key": "t0.c0, t1.c3 desc", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "loops": 1, "rows": 48000, "cost": 8.02633, "filtered": 99.5, "attached_condition": "t0.c4 is not null and t0.c0 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t0.c0"], "loops": 47760, "rows": 18, "cost": 893.9383424, "filtered": 100 } } ] } } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------+ | section | +-------------------+ | 6A BASE LIMIT 300 | +-------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.071838498 }, "query_block": { "select_id": 1, "cost": 901.9646724, "r_loops": 1, "r_total_time_ms": 2520.945481, "filesort": { "sort_key": "t0.c0, t1.c3 desc", "r_loops": 1, "r_total_time_ms": 1006.671023, "r_limit": 300, "r_used_priority_queue": true, "r_output_rows": 301, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t0", "access_type": "ALL", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "loops": 1, "r_loops": 1, "rows": 48000, "r_rows": 48000, "cost": 8.02633, "r_table_time_ms": 15.3391197, "r_other_time_ms": 6.097677774, "r_engine_stats": { "pages_accessed": 197 }, "filtered": 99.5, "r_total_filtered": 99.5, "attached_condition": "t0.c4 is not null and t0.c0 is not null", "r_filtered": 99.5 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t0.c0"], "loops": 47760, "r_loops": 47760, "rows": 18, "r_rows": 18, "cost": 893.9383424, "r_table_time_ms": 1091.029697, "r_other_time_ms": 400.693284, "r_engine_stats": { "pages_accessed": 1817028 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } ] } } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------+ | section | +---------------------+ | 6B HINTED LIMIT 300 | +---------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.090355779 }, "query_block": { "select_id": 1, "cost": 928.9865715, "r_loops": 1, "r_total_time_ms": 62.8918922, "nested_loop": [ { "read_sorted_file": { "r_rows": 43, "filesort": { "sort_key": "t0.c0, t1.c3 desc", "r_loops": 1, "r_total_time_ms": 61.1541123, "r_used_priority_queue": false, "r_output_rows": 72000, "r_sort_passes": 1, "r_buffer_size": "2047KiB", "r_sort_mode": "packed_sort_key,rowid", "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["idx_t1_c0"], "key": "idx_t1_c3_c0_c2", "key_length": "277", "used_key_parts": ["c3", "c0", "c2"], "loops": 1, "r_loops": 1, "rows": 72000, "r_rows": 72000, "cost": 10.89791872, "r_table_time_ms": 20.69653832, "r_other_time_ms": 41.2832198, "r_engine_stats": { "pages_accessed": 2075 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is not null", "r_filtered": 100, "using_index": true } } } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["idx_t0_c0", "idx_t0_c0_c1"], "key": "idx_t0_c0", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_order_limit_equality_key_costing.t1.c0"], "loops": 72000, "r_loops": 43, "rows": 12, "r_rows": 12, "cost": 918.0886528, "r_table_time_ms": 0.627488446, "r_other_time_ms": 0.124835444, "r_engine_stats": { "pages_accessed": 1118 }, "filtered": 99.5, "r_total_filtered": 58.13953488, "attached_condition": "t0.c4 is not null", "r_filtered": 58.13953488 } } ] } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------+ | section | +---------+ | done | +---------+