+-----------------+ | mariadb_version | +-----------------+ | 12.3.2-MariaDB | +-----------------+ +---------------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+-----------------------------------------+ | mariadb_orderby_limit_join_cache_costing.t2 | analyze | status | Engine-independent statistics collected | | mariadb_orderby_limit_join_cache_costing.t2 | analyze | status | OK | | mariadb_orderby_limit_join_cache_costing.t3 | analyze | status | Engine-independent statistics collected | | mariadb_orderby_limit_join_cache_costing.t3 | analyze | status | OK | | mariadb_orderby_limit_join_cache_costing.t0 | analyze | status | Engine-independent statistics collected | | mariadb_orderby_limit_join_cache_costing.t0 | analyze | status | OK | | mariadb_orderby_limit_join_cache_costing.t1 | analyze | status | Engine-independent statistics collected | | mariadb_orderby_limit_join_cache_costing.t1 | analyze | status | OK | +---------------------------------------------+---------+----------+-----------------------------------------+ +---------------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+-----------------------------------------+ | mariadb_orderby_limit_join_cache_costing.t2 | analyze | status | Engine-independent statistics collected | | mariadb_orderby_limit_join_cache_costing.t2 | analyze | status | OK | +---------------------------------------------+---------+----------+-----------------------------------------+ +---------------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+-----------------------------------------+ | mariadb_orderby_limit_join_cache_costing.t3 | analyze | status | Engine-independent statistics collected | | mariadb_orderby_limit_join_cache_costing.t3 | analyze | status | OK | +---------------------------------------------+---------+----------+-----------------------------------------+ +---------------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+-----------------------------------------+ | mariadb_orderby_limit_join_cache_costing.t0 | analyze | status | Engine-independent statistics collected | | mariadb_orderby_limit_join_cache_costing.t0 | analyze | status | OK | +---------------------------------------------+---------+----------+-----------------------------------------+ +---------------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------------+---------+----------+-----------------------------------------+ | mariadb_orderby_limit_join_cache_costing.t1 | analyze | status | Engine-independent statistics collected | | mariadb_orderby_limit_join_cache_costing.t1 | analyze | status | OK | +---------------------------------------------+---------+----------+-----------------------------------------+ +------------------------+ | section | +------------------------+ | SETTINGS_AFTER_ANALYZE | +------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+-----------------------------------+---------------------------------------+-------------------+--------------------+--------------------+--------------------+ | @@optimizer_switch | @@optimizer_prune_level | @@optimizer_join_limit_pref_ratio | @@optimizer_use_condition_selectivity | @@use_stat_tables | @@join_cache_level | @@join_buffer_size | @@sort_buffer_size | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+-----------------------------------+---------------------------------------+-------------------+--------------------+--------------------+--------------------+ | 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 | 0 | 0 | 4 | PREFERABLY | 2 | 262144 | 2097152 | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+-----------------------------------+---------------------------------------+-------------------+--------------------+--------------------+--------------------+ +------------+ | section | +------------+ | ROW_COUNTS | +------------+ +-----+------------+-------------+ | tbl | rows_total | c1_not_null | +-----+------------+-------------+ | t1 | 17100 | 5700 | | t3 | 8800 | NULL | +-----+------------+-------------+ +--------------------------------------------+ | section | +--------------------------------------------+ | JOIN_OUTPUT_CARDINALITY_BEFORE_ORDER_LIMIT | +--------------------------------------------+ +------------------------------+ | join_rows_before_order_limit | +------------------------------+ | 10032000 | +------------------------------+ +----------------------------------------------+ | section | +----------------------------------------------+ | CASE_1_BASE_BAD_jcl2_nnr_off_LIMIT_1_EXPLAIN | +----------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 4679.906231, "filesort": { "sort_key": "t3.c4, t3.c5 desc", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i6", "i7"], "loops": 1, "rows": 8800, "cost": 1.5273156, "filtered": 100 } }, { "range-checked-for-each-record": { "keys": ["i2", "i3"], "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c1"], "loops": 8800, "rows": 17100, "cost": 4678.378916, "filtered": 6.666666508 } } } ] } } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------+ | section | +----------------------------------------------+ | CASE_1_BASE_BAD_jcl2_nnr_off_LIMIT_1_ANALYZE | +----------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.081471649 }, "query_block": { "select_id": 1, "cost": 4679.906231, "r_loops": 1, "r_total_time_ms": 58761.71878, "filesort": { "sort_key": "t3.c4, t3.c5 desc", "r_loops": 1, "r_total_time_ms": 1498.965324, "r_limit": 1, "r_used_priority_queue": true, "r_output_rows": 2, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i6", "i7"], "loops": 1, "r_loops": 1, "rows": 8800, "r_rows": 8800, "cost": 1.5273156, "r_table_time_ms": 7.911687999, "r_other_time_ms": 147.2841382, "r_engine_stats": { "pages_accessed": 61 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } }, { "range-checked-for-each-record": { "keys": ["i2", "i3"], "r_keys": { "full_scan": 8800, "index_merge": 0, "range": { "i2": 0, "i3": 0 } }, "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c1"], "loops": 8800, "r_loops": 8800, "rows": 17100, "r_rows": 17100, "cost": 4678.378916, "r_table_time_ms": 39436.19518, "r_other_time_ms": 17592.88793, "r_engine_stats": { "pages_accessed": 723378 }, "filtered": 6.666666508, "r_total_filtered": 6.666666667, "r_filtered": 6.666666667 } } } ] } } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------+ | section | +----------------------------------------------------+ | CASE_2_DIAG_HINTED_JOIN_ORDER_jcl2_LIMIT_1_EXPLAIN | +----------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 11588.86411, "filesort": { "sort_key": "t3.c4, t3.c5 desc", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c1"], "loops": 1, "rows": 5700, "cost": 2.8802156, "filtered": 100, "index_condition": "t1.c1 is not null" } }, { "block-nl-join": { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i6", "i7"], "loops": 5700, "rows": 8800, "cost": 4660.032812, "filtered": 20 }, "buffer_type": "flat", "buffer_size": "105KiB", "join_type": "BNL", "attached_condition": "t3.c0 = t1.c0" } } ] } } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------------------------+ | section | +----------------------------------------------------+ | CASE_2_DIAG_HINTED_JOIN_ORDER_jcl2_LIMIT_1_ANALYZE | +----------------------------------------------------+ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.088927156 }, "query_block": { "select_id": 1, "cost": 11588.86411, "r_loops": 1, "r_total_time_ms": 11714.82816, "filesort": { "sort_key": "t3.c4, t3.c5 desc", "r_loops": 1, "r_total_time_ms": 1424.570457, "r_limit": 1, "r_used_priority_queue": true, "r_output_rows": 2, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["i2", "i3"], "key": "i3", "key_length": "9", "used_key_parts": ["c1"], "loops": 1, "r_loops": 1, "rows": 5700, "r_index_rows": 5700, "r_rows": 5700, "cost": 2.8802156, "r_table_time_ms": 7.279745614, "r_other_time_ms": 0.541098829, "r_engine_stats": { "pages_accessed": 11427 }, "filtered": 100, "r_total_filtered": 100, "index_condition": "t1.c1 is not null", "r_icp_filtered": 100, "r_filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i6", "i7"], "loops": 5700, "r_loops": 1, "rows": 8800, "r_rows": 8800, "cost": 4660.032812, "r_table_time_ms": 7.100481861, "r_other_time_ms": 0.414891396, "r_engine_stats": { "pages_accessed": 61 }, "filtered": 20, "r_total_filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "105KiB", "join_type": "BNL", "attached_condition": "t3.c0 = t1.c0", "r_loops": 5700, "r_filtered": 20, "r_unpack_time_ms": 1867.826197, "r_other_time_ms": 8324.123066, "r_effective_rows": 8800 } } ] } } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------+ | section | +-----------------------------------+ | RESULT_EQUIVALENCE_BASE_VS_HINTED | +-----------------------------------+ +------+---------------+ | side | ref0 | +------+---------------+ | BASE | low_ndv_3_6_3 | +------+---------------+ +--------+---------------+ | side | ref0 | +--------+---------------+ | HINTED | low_ndv_3_6_3 | +--------+---------------+ +-----------------------------------------------+ | section | +-----------------------------------------------+ | CASE_3_WORKAROUND_nnr_on_jcl2_LIMIT_1_EXPLAIN | +-----------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 4586.887401, "filesort": { "sort_key": "t3.c4, t3.c5 desc", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i6", "i7"], "loops": 1, "rows": 8800, "cost": 1.5273156, "filtered": 99.03408813 } }, { "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": ["i2", "i3"], "loops": 8715, "rows": 17100, "cost": 4585.360085, "filtered": 20, "attached_condition": "t1.c1 is not null" }, "buffer_type": "flat", "buffer_size": "256KiB", "join_type": "BNL", "attached_condition": "t1.c0 = t3.c0" } } ] } } } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------+ | section | +-----------------------------------------------+ | CASE_3_WORKAROUND_nnr_on_jcl2_LIMIT_1_ANALYZE | +-----------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.096482229 }, "query_block": { "select_id": 1, "cost": 4586.887401, "r_loops": 1, "r_total_time_ms": 12204.60187, "filesort": { "sort_key": "t3.c4, t3.c5 desc", "r_loops": 1, "r_total_time_ms": 1547.255939, "r_limit": 1, "r_used_priority_queue": true, "r_output_rows": 2, "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i6", "i7"], "loops": 1, "r_loops": 1, "rows": 8800, "r_rows": 8800, "cost": 1.5273156, "r_table_time_ms": 2.927101378, "r_other_time_ms": 0.806972338, "r_engine_stats": { "pages_accessed": 85 }, "filtered": 99.03408813, "r_total_filtered": 100, "r_filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": ["i2", "i3"], "loops": 8715, "r_loops": 2, "rows": 17100, "r_rows": 17100, "cost": 4585.360085, "r_table_time_ms": 14.16953404, "r_other_time_ms": 1.286344463, "r_engine_stats": { "pages_accessed": 155 }, "filtered": 20, "r_total_filtered": 33.33333333, "attached_condition": "t1.c1 is not null", "r_filtered": 33.33333333 }, "buffer_type": "flat", "buffer_size": "256KiB", "join_type": "BNL", "attached_condition": "t1.c0 = t3.c0", "r_loops": 8800, "r_filtered": 20, "r_unpack_time_ms": 2735.526332, "r_other_time_ms": 7811.796258, "r_effective_rows": 5700 } } ] } } } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------+ | section | +----------------------------------+ | CASE_4_FAST_jcl0_LIMIT_1_EXPLAIN | +----------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 14847.96583, "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t3.c4, t3.c5 desc", "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i6", "i7"], "loops": 1, "rows": 8800, "cost": 1.5273156, "filtered": 100, "attached_condition": "t3.c0 is not null" } } } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_orderby_limit_join_cache_costing.t3.c0"], "rowid_filter": { "range": { "key": "i3", "used_key_parts": ["c1"] }, "rows": 5700, "selectivity_pct": 33.33333333 }, "loops": 8800, "rows": 3420, "cost": 14846.43852, "filtered": 33.33333206, "attached_condition": "t1.c1 is not null" } } ] } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------------------------+ | section | +----------------------------------+ | CASE_4_FAST_jcl0_LIMIT_1_ANALYZE | +----------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.068510832 }, "query_block": { "select_id": 1, "cost": 14847.96583, "r_loops": 1, "r_total_time_ms": 7.338679928, "nested_loop": [ { "read_sorted_file": { "r_rows": 1, "filesort": { "sort_key": "t3.c4, t3.c5 desc", "r_loops": 1, "r_total_time_ms": 4.590391975, "r_used_priority_queue": false, "r_output_rows": 8800, "r_buffer_size": "2047KiB", "r_sort_mode": "sort_key,packed_addon_fields", "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i6", "i7"], "loops": 1, "r_loops": 1, "rows": 8800, "r_rows": 8800, "cost": 1.5273156, "r_table_time_ms": 2.8434667, "r_other_time_ms": 1.738174508, "r_engine_stats": { "pages_accessed": 61 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c0 is not null", "r_filtered": 100 } } } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_orderby_limit_join_cache_costing.t3.c0"], "rowid_filter": { "range": { "key": "i3", "used_key_parts": ["c1"] }, "rows": 5700, "selectivity_pct": 33.33333333, "r_rows": 5700, "r_lookups": 1, "r_selectivity_pct": 100, "r_buffer_size": 4275, "r_filling_time_ms": 2.471662327 }, "loops": 8800, "r_loops": 1, "rows": 3420, "r_index_rows": 1, "r_rows": 1, "cost": 14846.43852, "r_table_time_ms": 2.480140888, "r_other_time_ms": 0.010542939, "r_engine_stats": { "pages_accessed": 31 }, "filtered": 33.33333206, "r_total_filtered": 100, "attached_condition": "t1.c1 is not null", "r_filtered": 100 } } ] } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------+ | section | +-----------------------------------+ | CASE_5_FAST_jcl0_LIMIT_10_ANALYZE | +-----------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.113315124 }, "query_block": { "select_id": 1, "cost": 14847.96583, "r_loops": 1, "r_total_time_ms": 8.467069394, "nested_loop": [ { "read_sorted_file": { "r_rows": 1, "filesort": { "sort_key": "t3.c4, t3.c5 desc", "r_loops": 1, "r_total_time_ms": 5.710526674, "r_used_priority_queue": false, "r_output_rows": 8800, "r_buffer_size": "2047KiB", "r_sort_mode": "sort_key,packed_addon_fields", "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i6", "i7"], "loops": 1, "r_loops": 1, "rows": 8800, "r_rows": 8800, "cost": 1.5273156, "r_table_time_ms": 3.311935991, "r_other_time_ms": 2.391531609, "r_engine_stats": { "pages_accessed": 61 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c0 is not null", "r_filtered": 100 } } } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2", "i3"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_orderby_limit_join_cache_costing.t3.c0"], "rowid_filter": { "range": { "key": "i3", "used_key_parts": ["c1"] }, "rows": 5700, "selectivity_pct": 33.33333333, "r_rows": 5700, "r_lookups": 28, "r_selectivity_pct": 35.71428571, "r_buffer_size": 4275, "r_filling_time_ms": 2.464869978 }, "loops": 8800, "r_loops": 1, "rows": 3420, "r_index_rows": 28, "r_rows": 10, "cost": 14846.43852, "r_table_time_ms": 2.493369344, "r_other_time_ms": 0.010903748, "r_engine_stats": { "pages_accessed": 49 }, "filtered": 33.33333206, "r_total_filtered": 35.71428571, "attached_condition": "t1.c1 is not null", "r_filtered": 100 } } ] } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+