Optimizer chooses wrong index for ORDER BY
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Triaged
|
Medium
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
Similar to bug #1500639, but index is not partial in this case.
For query select * from ol where thread_id=10432 and site_id != 9939 order by id limit 3; Primary key on id chosen instead of composite index on thread_id and two other columns. This leads to processing more rows than needed and wrong EXPLAIN output:
explain select * from ol where thread_id=10432 and site_id != 9939 order by id limit 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE ol NULL index thread_id PRIMARY 4 NULL 37 7.22 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`ol`.`id` AS `id`,`test`
flush status;
select * from ol where thread_id=10432 and site_id != 9939 order by id limit 3;
id site_id thread_id type_id hidden_at
show status like 'Handler%';
Variable_name Value
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_
Handler_mrr_init 0
Handler_prepare 0
Handler_read_first 1
Handler_read_key 0
Handler_read_last 0
Handler_read_next 100000
Handler_read_prev 0
Handler_read_rnd 0
Handler_
Handler_rollback 0
Handler_savepoint 0
Handler_
Handler_update 0
Handler_write 0
Test case will be attached soon
tags: | added: upstream |
I see the same problem with MariaDB 10.1.6 (I had some hope...):
openxs@ ao756:~ /dbs/maria10. 1/mysql- test$ ./mtr bug1501457 dbs/maria10. 1/mysql- test/var openxs/ dbs/maria10. 1/mysql- test/var' ...
Logging: ./mtr bug1501457
vardir: /home/openxs/
Removing old var directory...
Creating var directory '/home/
Checking supported features...
MariaDB Version 10.1.6-MariaDB
- SSL connections supported
Collecting tests...
Installing system database...
======= ======= ======= ======= ======= ======= ======= ======= ======= ======= ======= =
TEST RESULT TIME (ms) or COMMENT ------- ------- ------- ------- ------- ------- ------- ------- ------- ----
-------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
main.bug1501457 [ fail ]
Test ended at 2015-10-06 15:58:49
CURRENT_TEST: main.bug1501457 dbs/maria10. 1/mysql- test/r/bug1501457.result 2015-10-06 15:58:24.577981808 +0300 dbs/maria10. 1/mysql- test/r/bug1501457.reject 2015-10-06 15:58:49.489980697 +0300 id`,`type_ id`,`hidden_ at`) 116666 DEFAULT CHARSET=latin1; external_ lock 0 icp_attempts 0 mrr_key_ refills 0 mrr_rowid_ refills 0 read_rnd_ deleted 0 read_rnd_ next 0 savepoint_ rollback 0
--- /home/openxs/
+++ /home/openxs/
@@ -0,0 +1,51 @@
+DROP TABLE IF EXISTS `ol`;
+Warnings:
+Note 1051 Unknown table 'test.ol'
+CREATE TABLE `ol` (
+`id` int(11) NOT NULL AUTO_INCREMENT,
+`site_id` int(11) DEFAULT NULL,
+`thread_id` int(11) DEFAULT NULL,
+`type_id` tinyint(3) unsigned DEFAULT NULL,
+`hidden_at` datetime DEFAULT NULL,
+PRIMARY KEY (`id`),
+KEY `thread_id` (`thread_
+) ENGINE=InnoDB AUTO_INCREMENT=
+Warnings:
+Warning 1286 Unknown storage engine 'InnoDB'
+Warning 1266 Using storage engine MyISAM for table 'ol'
+analyze table ol;
+Table Op Msg_type Msg_text
+test.ol analyze status OK
+explain select * from ol where thread_id=10432 and site_id != 9939 order by id limit 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE ol index thread_id PRIMARY 4 NULL 29 Using where
+flush status;
+select * from ol where thread_id=10432 and site_id != 9939 order by id limit 3;
+id site_id thread_id type_id hidden_at
+show status like 'Handler%';
+Variable_name Value
+Handler_commit 0
+Handler_delete 0
+Handler_discover 0
+Handler_
+Handler_
+Handler_icp_match 0
+Handler_mrr_init 0
+Handler_
+Handler_
+Handler_prepare 0
+Handler_read_first 1
+Handler_read_key 0
+Handler_read_last 0
+Handler_read_next 100000
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_
+Handler_
+Handler_rollback 0
+Handler_savepoint 0
+Handler_
+Handler_tmp_update 0
+Handler_tmp_write 0
+Handler_update 0
+Handler_write 0
...