Details
Description
base tables and content:
| customers | CREATE TABLE `customers` ( `customers_id` int(11) NOT NULL AUTO_INCREMENT, `customers_username` varchar(60) NOT NULL DEFAULT '', PRIMARY KEY (`customers_id`) ) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=latin1 | | customers_info | CREATE TABLE `customers_info` ( `customers_id` bigint(20) unsigned NOT NULL DEFAULT '0', `account_created_date` datetime DEFAULT NULL, PRIMARY KEY (`customers_id`), KEY `account_created_date_migrated_status` (`account_created_date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | insert into customers (customers_id) select * from seq_1_to_50000; insert into customers_info select customers_id,NOW() - INTERVAL customers_id DAY from customers;
An ideal query plan:
explain select customers.customers_id, customers_info.account_created_date from customers JOIN customers_info USING(customers_id) ORDER BY account_created_date DESC LIMIT 1; +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+--------------------------+ | 1 | SIMPLE | customers_info | index | PRIMARY | account_created_date_migrated_status | 9 | NULL | 1 | Using index | | 1 | SIMPLE | customers | eq_ref | PRIMARY | PRIMARY | 4 | test.customers_info.customers_id | 1 | Using where; Using index | +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+--------------------------+ 2 rows in set (0.02 sec)
When we add another field to result, customers.customers_username then we end up doing a temporary and filesort.
MariaDB [test]> explain select customers.customers_id, customers.customers_username, customers_info.account_created_date from customers JOIN customers_info USING(customers_id) ORDER BY account_created_date DESC LIMIT 1
-> ;
+------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------------+
| 1 | SIMPLE | customers_info | index | PRIMARY | account_created_date_migrated_status | 9 | NULL | 50386 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | customers | eq_ref | PRIMARY | PRIMARY | 4 | test.customers_info.customers_id | 1 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan |
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-8367 poor query plan: remove uneeded temp/filesort - mrr=on and join_cache_level>2
-
- Open
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
config:
optimizer_switch = 'mrr=on,mrr_sort_keys=on,index_merge=off,index_merge_intersection=off,index_merge_union=off,index_merge_sort_union=off' join_cache_level=8 # engine independant stats use_stat_tables = complementary histogram_size=255 optimizer_use_condition_selectivity=4 innodb_stats_traditional = false
same result after ANALYZE TABLE
{tbl} and ANALYZE TABLE {tbl}PERSISTENT FOR ALL
MariaDB [test]> explain select customers.customers_id, customers.customers_username, customers_info.account_created_date from customers JOIN customers_info USING(customers_id) ORDER BY account_created_date DESC LIMIT 1; +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+-------------+ | 1 | SIMPLE | customers_info | index | PRIMARY | account_created_date_migrated_status | 9 | NULL | 1 | Using index | | 1 | SIMPLE | customers | eq_ref | PRIMARY | PRIMARY | 4 | test.customers_info.customers_id | 1 | Using where | +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+-------------+ 2 rows in set (0.00 sec)
MariaDB [test]> set optimizer_switch='mrr=on,mrr_sort_keys=on'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> explain select customers.customers_id, customers.customers_username, customers_info.account_created_date from customers JOIN customers_info USING(customers_id) ORDER BY account_created_date DESC LIMIT 1; +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+-------------+ | 1 | SIMPLE | customers_info | index | PRIMARY | account_created_date_migrated_status | 9 | NULL | 1 | Using index | | 1 | SIMPLE | customers | eq_ref | PRIMARY | PRIMARY | 4 | test.customers_info.customers_id | 1 | Using where | +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+-------------+ 2 rows in set (0.00 sec)
MariaDB [test]> set join_cache_level=8; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> explain select customers.customers_id, customers.customers_username, customers_info.account_created_date from customers JOIN customers_info USING(customers_id) ORDER BY account_created_date DESC LIMIT 1; +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------------+ | 1 | SIMPLE | customers_info | index | PRIMARY | account_created_date_migrated_status | 9 | NULL | 50386 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | customers | eq_ref | PRIMARY | PRIMARY | 4 | test.customers_info.customers_id | 1 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan | +------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------------+ 2 rows in set (0.00 sec)
However, I don't see a regression from MyISAM:
MariaDB [test]> alter table customers engine=MyISAM; Query OK, 50000 rows affected (0.38 sec) Records: 50000 Duplicates: 0 Warnings: 0 MariaDB [test]> alter table customers_info engine=MyISAM; Query OK, 50000 rows affected (0.13 sec) Records: 50000 Duplicates: 0 Warnings: 0 MariaDB [test]> explain select customers.customers_id, customers.customers_username, customers_info.account_created_date from customers JOIN customers_info USING(customers_id) ORDER BY account_created_date DESC LIMIT 1; +------+-------------+----------------+--------+---------------+---------+---------+----------------------------------+-------+--------------------------------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+--------+---------------+---------+---------+----------------------------------+-------+--------------------------------------------------------------------------------------------------------------+ | 1 | SIMPLE | customers_info | ALL | PRIMARY | NULL | NULL | NULL | 50000 | Using temporary; Using filesort | | 1 | SIMPLE | customers | eq_ref | PRIMARY | PRIMARY | 4 | test.customers_info.customers_id | 1 | Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan | +------+-------------+----------------+--------+---------------+---------+---------+----------------------------------+-------+--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
> However, I don't see a regression from MyISAM:
quite right. i didn't do an analyze table after changing the engine type.
MyISAM is much more behaved: