MySQL Bugs: #69013: Poor execution plan when ORDER BY with LIMIT X

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	CPU Architecture:	Any

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.

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

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.

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)

Thanks for the upload, I verified as described on latest 5.6.12 source.

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%';

Trace of query with and without LIMIT clause

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

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.

There are more details on the query and tables in
Bug #68979 performance regression of MySQL 5.6.10?

Noted in 5.6.12, 5.7.2 changelogs.

The optimizer could choose a poor execution plan for queries with
ORDER BY ... LIMIT.

Bug remains evident in 5.6.12

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,

New (related?) bug opened as requested.

http://bugs.mysql.com/bug.php?id=69410

Thank you