| Bug #81812 | Query Planner chooses wrong index for ORDER BY LIMIT 1 and limit 2 and more | ||
|---|---|---|---|
| Submitted: | 11 Jun 2016 14:40 | Modified: | 14 Jul 2016 8:21 |
| Reporter: | manish kumar | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.6 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | Any | |
[11 Jun 2016 20:20]
MySQL Verification Team
Are you using the latest released version 5.6.31? otherwise check it. If the issue is still present please provide the test case with create table statements and insert data (if needed). Thanks.
[13 Jun 2016 16:09]
manish kumar
I am using version: 5.6.26-74.0-log
[15 Jul 2016 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: It's very strange. For limit 1 optimiser is picking other index and for limit 2 using different indexes. mysql> explain SELECT column1 FROM table WHERE operator_id = 23672 AND effective_date <= '2016-06-10 23:59:59' AND created_at <='2016-06-11 02:59:59' ORDER BY id DESC LIMIT 1\G key: PRIMARY Extra: Using where 1 row in set (0.00 sec) mysql> explain SELECT column FROM table WHERE operator_id = 23672 AND date <= 'xxxxxxxx' AND created_at <='xxxxxxx' ORDER BY id DESC LIMIT 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE key: combo_operator_acc_effect_date Extra: Using index condition; Using where; Using filesort 1 row in set (0.00 sec) How to repeat: This is repeating very frequently. Suggested fix: When i am adding force index, the this is working fine.