MySQL Forums
Forum List  »  Performance

Slow performance when using LIMIT 1000
Posted by: Christian Esborg
Date: February 25, 2015 07:43AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow performance when using LIMIT 1000
1679
February 25, 2015 07:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.