We're updating the issue view to help you get more done.Learn more

Unable to skip filesort when using implicit extended key

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.

Status