Slow performance when using LIMIT 1000
It looks like MySQL v5.6 isn't using the table indexes in certain circumstances, leading to extremely slow performance.
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)
Does anyone know if this performance issue been solved in MySql v5.7?
Or is there a workaround for this issue?