Bug #69013 Poor execution plan when ORDER BY with LIMIT X
Submitted: 19 Apr 2013 18:45 Modified: 30 May 2013 2:11
Reporter: Marcin Krzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.6.10-log OS:Linux
Assigned to: Jørgen Løland

[19 Apr 2013 18:45] Marcin Krzan
Description:
Optimizer is not using multi-column index and is performing full column scan when ORDER BY with LIMIT X are using together in a 2 or more tables join query.

How to repeat:
EXPLAIN SELECT b.idBetslip FROM betslips b, events_races r WHERE b.idAccount = 1110 AND b.eventDate >= "2013-04-17" AND b.idRace = r.idRace ORDER BY r.postTime LIMIT 1; 
+----+-------------+-------+--------+--------------------+---------+---------+-------------------+----------+----------------------------------------------+
| id | select_type | table | type   | possible_keys      | key     | key_len | ref               | rows     | Extra                                        |
+----+-------------+-------+--------+--------------------+---------+---------+-------------------+----------+----------------------------------------------+
|  1 | SIMPLE      | b     | ALL    | idRace,myBetsIndex | NULL    | NULL    | NULL              | 27967601 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY            | PRIMARY | 3       | sunshine.b.idRace |        1 | NULL                                         |
+----+-------------+-------+--------+--------------------+---------+---------+-------------------+----------+----------------------------------------------+

used tables:

CREATE TABLE `betslips` (
  `idBetslip` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idRace` mediumint(8) unsigned NOT NULL,
  `idAccount` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `eventDate` date NOT NULL,
  PRIMARY KEY (`idBetslip`),
  KEY `idTransaction` (`idTransaction`),
  KEY `idRace` (`idRace`),
  KEY `myBetsIndex` (`idAccount`,`eventDate`)
) ENGINE=InnoDB AUTO_INCREMENT=28274469 DEFAULT CHARSET=utf8

CREATE TABLE `events_races` (
  `idRace` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `postTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
  PRIMARY KEY (`idRace`),
  KEY `postTime` (`postTime`)
) ENGINE=InnoDB AUTO_INCREMENT=850489 DEFAULT CHARSET=utf8

executing query just without LIMIT X is making optimization engine to choose right plan and use the key myBetsIndex:

EXPLAIN SELECT b.idBetslip FROM betslips b, events_races r WHERE b.idAccount = 1110 AND b.eventDate >= "2013-04-17" AND b.idRace = r.idRace ORDER BY r.postTime;
+----+-------------+-------+--------+--------------------+-------------+---------+-------------------+------+--------------------------------------------------------+
| id | select_type | table | type   | possible_keys      | key         | key_len | ref               | rows | Extra                                                  |
+----+-------------+-------+--------+--------------------+-------------+---------+-------------------+------+--------------------------------------------------------+
|  1 | SIMPLE      | b     | range  | idRace,myBetsIndex | myBetsIndex | 6       | NULL              |   10 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY            | PRIMARY     | 3       | sunshine.b.idRace |    1 | NULL                                                   |
+----+-------------+-------+--------+--------------------+-------------+---------+-------------------+------+--------------------------------------------------------+

We noticed the problem after upgrading source distribution of MySQL from 5.1.42-log to 5.6.10-log. In 5.1.42-log execution plan was correct.

Suggested fix:
Using FORCE INDEX clause or removing LIMIT X is making optimizer to use correct execution plan.
[20 Apr 2013 5:23] Umesh Shastry
Thank you for the bug report.

Could you confirm if this is repeatable with latest GA 5.6.11? I cannot repeat reported behavior with the dummy data, could you please provide the test data to reproduce at our end? 

Thanks,
Umesh
[20 Apr 2013 10:42] Shane Bester
i also couldn't repeat a problem with fake data. btw, the table structure given here is incomplete, maybe that was a cause?  we really need you to check 5.6.11 and if still having a problem, consider to upload a compressed mysqldump of the tables.
[21 Apr 2013 18:07] Marcin Krzan
I just uploaded a dump to your ftp using anonymous username - filename: bug-data-69013.tgz

I executed those 2 queries there - one with LIMIT 1 and one without to see the difference in execution plan:

mysql> EXPLAIN SELECT b.idBetslip FROM betslips b, events_races r WHERE b.idAccount = 5 AND b.eventDate >= "2012-02-05" AND b.idRace = r.idRace ORDER BY r.postTime limit 1; 
+----+-------------+-------+--------+--------------------+---------+---------+-------------------+---------+----------------------------------------------+
| id | select_type | table | type   | possible_keys      | key     | key_len | ref               | rows    | Extra                                        |
+----+-------------+-------+--------+--------------------+---------+---------+-------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | b     | ALL    | idRace,myBetsIndex | NULL    | NULL    | NULL              | 1152602 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | r     | eq_ref | PRIMARY            | PRIMARY | 3       | mysqlbug.b.idRace |       1 | NULL                                         | 
+----+-------------+-------+--------+--------------------+---------+---------+-------------------+---------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT b.idBetslip FROM betslips b, events_races r WHERE b.idAccount = 5 AND b.eventDate >= "2012-02-05" AND b.idRace = r.idRace ORDER BY r.postTime; 
+----+-------------+-------+--------+--------------------+-------------+---------+-------------------+------+--------------------------------------------------------+
| id | select_type | table | type   | possible_keys      | key         | key_len | ref               | rows | Extra                                                  |
+----+-------------+-------+--------+--------------------+-------------+---------+-------------------+------+--------------------------------------------------------+
|  1 | SIMPLE      | b     | range  | idRace,myBetsIndex | myBetsIndex | 6       | NULL              |  135 | Using index condition; Using temporary; Using filesort | 
|  1 | SIMPLE      | r     | eq_ref | PRIMARY            | PRIMARY     | 3       | mysqlbug.b.idRace |    1 | NULL                                                   | 
+----+-------------+-------+--------+--------------------+-------------+---------+-------------------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)
[21 Apr 2013 18:53] Shane Bester
Thanks for the upload, I verified as described on latest 5.6.12 source.
[21 Apr 2013 18:56] Shane Bester
the optimizer does really more work when using limit 1, as can be seen using the comparison:

flush status;
select .... limit 1;
show status like '%handler%';
flush status;
select .... ;
show status like '%handler%';
[30 Apr 2013 18:20] Jørgen Thomsen
Trace of query with and without LIMIT clause

Attachment: optimizer.log (application/octet-stream, text), 132.49 KiB.

[30 Apr 2013 18:22] Jørgen Thomsen
It appears, that the same query runs 5 times slower with a LIMIT clause than without it.

I have uploaded an optimizer trace for both queries.
[30 Apr 2013 18:27] Jørgen Thomsen
There are more details on the query and tables in
Bug #68979 performance regression of MySQL 5.6.10?
[30 May 2013 2:11] Paul Dubois
Noted in 5.6.12, 5.7.2 changelogs.

The optimizer could choose a poor execution plan for queries with
ORDER BY ... LIMIT.
[3 Jun 2013 21:13] Michael Riediger
Bug remains evident in 5.6.12
[5 Jun 2013 11:02] Jørgen Løland
As far as I can tell, the problem reported by OP has been fixed. If you have found another bug I kindly suggest that you open a new bug report with a reproducible test case. 

If OP does not agree that the issue has been fixed, please reopen the bug.

Thanks,
[5 Jun 2013 20:45] Michael Riediger
New (related?) bug opened as requested.

http://bugs.mysql.com/bug.php?id=69410
[6 Jun 2013 6:43] Jørgen Løland
Thank you