|
mrr=on + join_cache_level> 2 - sub optimal query: index+ temporary and filesort used where index is sufficient
data set up:
CREATE TABLE `customers_info` ( `customers_id` bigint(20) unsigned NOT NULL DEFAULT '0', `account_created_date` datetime DEFAULT NULL, status tinyint NOT NULL;
|
|
CREATE TABLE `customers_info` ( `customers_id` bigint(20) unsigned NOT NULL DEFAULT '0', `account_created_date` datetime DEFAULT NULL, status tinyint NOT NULL, PRIMARY KEY (`customers_id`), KEY `status_account_created_date` (status,account_created_date));
|
|
insert into customers (customers_id) select * from seq_1_to_50000;
|
insert into customers_info select customers_id,NOW() - INTERVAL customers_id DAY, customers_id MOD 50 from customers;
|
analyze table customers_info; analyze table customers;
|
results:
set optimizer_switch = 'mrr=on,mrr_sort_keys=on', join_cache_level=8;
|
explain extended select customers.customers_id, customers.customers_username,customers_info.account_created_date from customers JOIN customers_info USING(customers_id) WHERE status=30 ORDER BY account_created_date DESC LIMIT 1
|
|
+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+--------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+-------------------------------------------------------------------+
|
| 1 | SIMPLE | customers_info | ref | PRIMARY,status_account_created_date | status_account_created_date | 1 | const | 999 | 100.00 | Using index; Using temporary; Using filesort |
|
| 1 | SIMPLE | customers | eq_ref | PRIMARY | PRIMARY | 4 | test.customers_info.customers_id | 1 | 100.00 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan |
|
+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+-------------------------------------------------------------------+
|
Expected results - as per mrr=on and join_cache_level=2 "using Index" rather than "Using index; Using temporary; Using filesort"
MariaDB [test]> select @@optimizer_switch, @@join_cache_level\G
|
*************************** 1. row ***************************
|
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=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=off,table_elimination=on,extended_keys=on,exists_to_in=on
|
@@join_cache_level: 2
|
|
MariaDB [test]> explain extended select customers.customers_id, customers.customers_username,customers_info.account_created_date from customers JOIN customers_info USING(customers_id) WHERE status=30 ORDER BY account_created_date DESC LIMIT 1;
|
+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+--------------------------+
|
| 1 | SIMPLE | customers_info | ref | PRIMARY,status_account_created_date | status_account_created_date | 1 | const | 999 | 100.00 | Using where; Using index |
|
| 1 | SIMPLE | customers | eq_ref | PRIMARY | PRIMARY | 4 | test.customers_info.customers_id | 1 | 100.00 | Using where |
|
+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+--------------------------+
|
Does this even need to "using where" on customers_info as its all in the index and is in order?
Like MDEV-8350, removing customers.customers_username from the select results seems to general a reasonable query.
|