Details
Major
Description
The optimizer is able to skip filesort when PK columns are explicitly defined in the secondary index, but not able to do so when they are in the index extension.
Test dataset:
CREATE TABLE tb_bug1 ( pk1 int(11) NOT NULL, pk2 varchar(64) NOT NULL, col1 varchar(16) DEFAULT NULL, PRIMARY KEY (pk1,pk2), KEY key1 (pk1,col1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE tb_bug2 ( pk1 int(11) NOT NULL, pk2 varchar(64) NOT NULL, col1 varchar(16) DEFAULT NULL, PRIMARY KEY (pk1,pk2), KEY key1 (pk1,col1,pk2) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `tb_bug1` VALUES (12321321,'a8f5f167f44f4964e6c998dee827110c','video'), (12321321,'d77a17a3659ffa60c54e0ea17b6c6d16','video'), (12321321,'wwafdsafdsafads','video'), (12321321,'696aa249f0738e8181957dd57c2d7d0b','video-2014-09-23'), (12321321,'802f9f29584b486f356693e3aa4ef0af','video=sdsd'), (12321321,'2f94543ff74aab82e9a058b4e8316d75','video=sdsdsds'), (12321321,'c1316b9df0d203fd1b9035308de52a0a','video=sdsdsdsdsd'); insert into tb_bug2 select * from tb_bug1;
explain SELECT pk2 FROM tb_bug1 USE INDEX(key1) WHERE pk1 = 123 AND col1 = 'video' ORDER BY pk2 DESC LIMIT 21; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tb_bug1 ref key1 key1 55 const,const 1 Using where; Using index; Using filesort explain SELECT pk2 FROM tb_bug2 USE INDEX(key1) WHERE pk1 = 123 AND col1 = 'video' ORDER BY pk2 DESC LIMIT 21; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tb_bug2 ref key1 key1 55 const,const 1 Using where; Using index
Note that the first query uses "Using filesort" while the second one doesn't.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The problem is here in test_if_order_by_key() function:
/*
We are at the end of the key. Check if the engine has the primary
key as a suffix to the secondary keys. If it has continue to check
the primary key as a suffix.
*/
if (!on_pk_suffix && (table->key_info[idx].ext_key_part_map & 1) &&
(table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) &&
table->s->primary_key != MAX_KEY &&
table->s->primary_key != idx)
{
Here, (table->key_info[idx].ext_key_part_map & 1) == 0. This condition is false, and I don't see any reason why we should require that it is true.
(Investigated what ext_key_part_map is. See here: http://lists.askmonty.org/pipermail/commits/2014-September/006655.html)
MySQL 5.6 has different code, but it's still incorrect. Their code doesn't take into account that secondary index may explicitly include columns from the primary index.
Fix pushed to the 10.1-based tree with ORDER BY optimization fixes: https://github.com/MariaDB/server/tree/bb-10.1-orderby-fixes
Elena Stepanova, I need a testing pass for this fix. It is ok to test together with other fixes in 10.1-orderby-fixes.
MDEV-6657andMDEV-6402.