Slow performance when using LIMIT 1000

[Christian Esborg]

Hi all !
It looks like MySQL v5.6 isn't using the table indexes in certain circumstances, leading to extremely slow performance.

Here's an example of a query being sent to MySQL:

SELECT * FROM GroupCall WHERE ((CallInitiated >= '2015-02-16 20:10:00' AND CallInitiated <= '2015-02-16 20:50:00') OR (CallBegin >= '2015-02-16 20:10:00' AND CallBegin <= '2015-02-16 20:50:00')) ORDER BY CallInitiated DESC LIMIT 1000;

If I use MySQL's 'EXPLAIN' command in conjunction with this query, we can see that MySQL doesn't intend to use any indexes to answer the query, and will search all 7,734,719 database rows:

mysql> EXPLAIN SELECT * FROM GroupCall WHERE ((CallInitiated >= '2015-02-16 20:10:00' AND CallInitiated <= '2015-02-16 20:50:00') OR (CallBegin >= '2015-02-16 20:10:00' AND CallBegin <= '2015-02-16 20:50:00')) ORDER BY CallInitiated DESC LIMIT 1000;
+----+-------------+-----------+------+-------------------------+------+---------+------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-------------------------+------+---------+------+---------+-----------------------------+
| 1 | SIMPLE | GroupCall | ALL | CallInitiated,CallBegin | NULL | NULL | NULL | 7734719 | Using where; Using filesort |
+----+-------------+-----------+------+-------------------------+------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)

Strangely, if we increase or remove the 'LIMIT' value, the query performs correctly by using the available indexes and only searching 2724 rows:

mysql> EXPLAIN SELECT * FROM GroupCall WHERE ((CallInitiated >= '2015-02-16 20:10:00' AND CallInitiated <= '2015-02-16 20:50:00') OR (CallBegin >= '2015-02-16 20:10:00' AND CallBegin <= '2015-02-16 20:50:00')) ORDER BY CallInitiated DESC LIMIT 3000;
+----+-------------+-----------+-------------+-------------------------+-------------------------+---------+------+------+------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+-------------------------+-------------------------+---------+------+------+------------------------------------------------------------------------+
| 1 | SIMPLE | GroupCall | index_merge | CallInitiated,CallBegin | CallInitiated,CallBegin | 6,6 | NULL | 2724 | Using sort_union(CallInitiated,CallBegin); Using where; Using filesort |
+----+-------------+-----------+-------------+-------------------------+-------------------------+---------+------+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

The database server with this issue is running MySQL 5.6.17. As a further test, if I try the original query against a similarly sized Log Server database on MySQL 5.5.37, then it behaves correctly:

mysql> EXPLAIN SELECT * FROM GroupCall WHERE ((CallInitiated >= '2015-02-16 20:10:00' AND CallInitiated <= '2015-02-16 20:50:00') OR (CallBegin >= '2015-02-1
6 20:10:00' AND CallBegin <= '2015-02-16 20:50:00')) ORDER BY CallInitiated DESC LIMIT 1000;
+----+-------------+-----------+-------------+-------------------------+-------------------------+---------+------+-------+------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+-------------------------+-------------------------+---------+------+-------+------------------------------------------------------------------------+
| 1 | SIMPLE | GroupCall | index_merge | CallInitiated,CallBegin | CallInitiated,CallBegin | 9,9 | NULL | 13313 | Using sort_union(CallInitiated,CallBegin); Using where; Using filesort |
+----+-------------+-----------+-------------+-------------------------+-------------------------+---------+------+-------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

Are there any work-arounds for this issue?

Thanks in advance.

BR Christian