Bug #78651 Optimizer chooses wrong index for ORDER BY

• Submitted: 30 Sep 2015 18:44
• Modified: 1 Oct 2015 8:33
• Reporter: Sveta Smirnova (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.5.45, 5.6.26, 5.7.8, 5.5.47, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[30 Sep 2015 18:44] Sveta Smirnova

Description:
Similar to bug #78612, 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

How to repeat:
Test case will be attached soon

[1 Oct 2015 8:33] MySQL Verification Team

Hello Sveta,

Thank you for the report.
Observed as described with 5.5.47, 5.6.28 and 5.7.10 builds(Test case used from 1501457).

Thanks,
Umesh

[14 Apr 2016 9:16] Sveta Smirnova

Test case which looks like I forgot to attach

Attachment: bug1501457.test (application/octet-stream, text), 2.71 MiB.

[20 Oct 2016 12:35] MySQL Verification Team

Bug #83298 marked as duplicate of this