Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.13, 10.1.0
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.