Optimizer chooses wrong index for ORDER BY

[Sveta Smirnova]

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`.`ol`.`site_id` AS `site_id`,`test`.`ol`.`thread_id` AS `thread_id`,`test`.`ol`.`type_id` AS `type_id`,`test`.`ol`.`hidden_at` AS `hidden_at` from `test`.`ol` where ((`test`.`ol`.`thread_id` = 10432) and (`test`.`ol`.`site_id` <> 9939)) order by `test`.`ol`.`id` limit 3
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_external_lock 2
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_read_rnd_next 0
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 0

Test case will be attached soon

[Valerii Kravchuk (valerii-kravchuk)]

I see the same problem with MariaDB 10.1.6 (I had some hope...):

openxs@ao756:~/dbs/maria10.1/mysql-test$ ./mtr bug1501457
Logging: ./mtr bug1501457
vardir: /home/openxs/dbs/maria10.1/mysql-test/var
Removing old var directory...
Creating var directory '/home/openxs/dbs/maria10.1/mysql-test/var'...
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
--- /home/openxs/dbs/maria10.1/mysql-test/r/bug1501457.result 2015-10-06 15:58:24.577981808 +0300
+++ /home/openxs/dbs/maria10.1/mysql-test/r/bug1501457.reject 2015-10-06 15:58:49.489980697 +0300
@@ -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_id`,`type_id`,`hidden_at`)
+) ENGINE=InnoDB AUTO_INCREMENT=116666 DEFAULT CHARSET=latin1;
+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_external_lock 0
+Handler_icp_attempts 0
+Handler_icp_match 0
+Handler_mrr_init 0
+Handler_mrr_key_refills 0
+Handler_mrr_rowid_refills 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_read_rnd_deleted 0
+Handler_read_rnd_next 0
+Handler_rollback 0
+Handler_savepoint 0
+Handler_savepoint_rollback 0
+Handler_tmp_update 0
+Handler_tmp_write 0
+Handler_update 0
+Handler_write 0
...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1655