Why is MySQL slower when using LIMIT in my query?

Question

I'm trying to figure out why one of my queries is slow and how I can fix it, but I'm a bit puzzled by the results.

I have an orders table with approximately 80 columns and 775179 rows, and I make the following query:

SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC LIMIT 200

which returns 38 rows in 4.5s

When uninstalling ORDER BY I get a nice improvement:

SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL LIMIT 200

38 lines at 0.30s

But when deleting LIMIT , without touching ORDER BY , I get even better result:

SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC

38 lines at 0.10s (??)

Why is my LIMIT so hungry?

GOING ON

I tried a few things before posting my answer, and, noticing that I have an index on creation_date (which is a datetime ), I deleted it and the first query now works at 0.10s. Why is this?

EDIT

Ok, I have indexes on the rest of the columns where I have it.

 mysql> explain SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC LIMIT 200; +----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+ | 1 | SIMPLE | orders | index | id_state_idx,id_mp_idx | creation_date | 5 | NULL | 1719 | Using where | +----+-------------+--------+-------+------------------------+---------------+---------+------+------+-------------+ 

1 row per set (0.00 s)

 mysql> explain SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC; +----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+ | 1 | SIMPLE | orders | range | id_state_idx,id_mp_idx | id_mp_idx | 3 | NULL | 87502 | Using index condition; Using where; Using filesort | +----+-------------+--------+-------+------------------------+-----------+---------+------+-------+----------------------------------------------------+ 

Answer 1

Indexes do not necessarily improve performance. To better understand what is happening, this will help if you include explain for different queries.

My best guess is that you have an index in id_state or even id_state, id_mp , which you can use to execute the where clause. If so, the first query without order by will use this index. It should be pretty fast. Even without an index, this requires sequential scanning of the pages in the orders table, which can be quite fast.

Then, when you add an index to creation_date , MySQL decides to use that index instead of order by . This requires reading each row in the index, and then fetching the corresponding data page to check where conditions and return columns (if there is a match). This reading is very inefficient because it is not in the "page", but rather indicated by the index. Random reads can be very inefficient.

Worse, although you have a limit , you still need to read the entire table, because you need a whole set of results. Although you kept sorting by 38 records, you created a massively inefficient query.

By the way, this situation worsens significantly if the orders table does not fit in the available memory. Then you have a condition called “shredding,” where each new record tends to generate a new I / O read. So, if there are 100 entries on a page, the page can be read 100 times.

You can make all of these queries faster by pointing the index to orders(id_state, id_mp, creation_date) . The where clause will use the first two columns, and order by will use the last.

Answer 2

The same problem occurred in my project, I did some tests and found that LIMIT is slow due to string search

See: MySQL ORDER BY / LIMIT performance: search at end of line

So the solution is:

(A) when using LIMIT, select not all columns, but only PK columns

(B) Select all the necessary columns and then attach them to the result set (A)

SQL should like:

 SELECT * FROM orders O1 <=== this is what you want JOIN ( SELECT ID <== fetch the PK column only, this should be fast FROM orders WHERE [your query condition] <== filter record by condition ORDER BY [your order by condition] <== control the record order LIMIT 2000, 50 <== filter record by paging condition ) as O2 ON O1.ID = O2.ID ORDER BY [your order by condition] <== control the record order 

in my db

old SQL, which selects all columns using "LIMIT 21560, 20", costs about 4.484 s.

new sql costs only 0.063s. New about 71 times faster