[MDEV-8350] poor query plan: ideal index from ORDER BY field is not used when extra columns retrieved - mrr=on and join_cache_level>2 Created: 2015-06-22  Updated: 2022-09-12  Resolved: 2022-09-12

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.0.19
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Sergei Petrunia
Resolution: Won't Fix Votes: 1
Labels: mrr, order-by-optimization

Issue Links:
Relates
relates to MDEV-8367 poor query plan: remove uneeded temp/... Open

 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 |



 Comments   
Comment by Daniel Black [ 2015-06-22 ]

MyISAM is much more behaved:

MariaDB [test]> alter table customers_info engine=MyISAM;
 
 
 
MariaDB [test]> alter table customers engine=MyISAM;
 
 
 
MariaDB [test]> 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 |                          |
 
|    1 | SIMPLE      | customers      | eq_ref | PRIMARY       | PRIMARY                              | 4       | test.customers_info.customers_id |    1 | Using where; Using index |
 
+------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+--------------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> 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 |                          |
 
|    1 | SIMPLE      | customers      | eq_ref | PRIMARY       | PRIMARY                              | 4       | test.customers_info.customers_id |    1 | Using where; Using index |
 
+------+-------------+----------------+--------+---------------+--------------------------------------+---------+----------------------------------+------+--------------------------+

Comment by Daniel Black [ 2015-06-23 ]

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

Comment by Elena Stepanova [ 2015-06-23 ] 

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)

Comment by Elena Stepanova [ 2015-06-23 ]

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)

Comment by Daniel Black [ 2015-06-23 ]

> However, I don't see a regression from MyISAM:

quite right. i didn't do an analyze table after changing the engine type.

Comment by Sergei Golubchik [ 2022-09-12 ]

10.0 was EOLed in March 2019

Generated at Thu Feb 08 07:26:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.