MySQL Forums
Forum List  »  MyISAM

Slow performance when using LIMIT 1000
Posted by: Christian Esborg
Date: February 26, 2015 04:32AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow performance when using LIMIT 1000
2510
February 26, 2015 04:32AM
1277
February 26, 2015 05:03PM


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.