Select is much slower when selecting latest records

Question

A table with about 70K records is displayed on a site, showing 50 records per page. Pagination is done with limit offset,50 on the query, and the records can be ordered on different columns.

Browsing the latest pages (so the offset is around 60,000) makes the queries much slower than when browsing the first pages (about 10x)

Is this an issue of using the limit command? Are there other ways to get the same results?

Answer 1

It's how MySQL deals with limits. If it can sort on an index (and the query is simple enough) it can stop searching after finding the first offset + limit rows. So LIMIT 0,10 means that if the query is simple enough, it may only need to scan 10 rows. But LIMIT 1000,10 means that at minimum it needs to scan 1010 rows. Of course, the actual number of rows that need to be scanned depend on a host of other factors. But the point here is that the lower the limit + offset, the lower that the lower-bound on the number of rows that need to be scanned is...

As for workarounds, I would optimize your queries so that the query itself without the LIMIT clause is as efficient as possible. EXPLAIN is you friend in this case...

Answer 2

With large offsets, MySQL needs to browse more records.

Even if the plan uses filesort (which means that all records should be browsed), MySQL optimizes it so that only $offset + $limit top records are sorted, which makes it much more efficient for lower values of $offset.

The typical solution is to index the columns you are ordering on, record the last value of the columns and reuse it in the subsequent queries, like this:

SELECT  *
FROM    mytable
ORDER BY
        value, id
LIMIT 0, 10

which outputs:

value  id

1      234
3      57
4      186
5      457
6      367
8      681
10     366
13     26
15     765
17     345  -- this is the last one

To get to the next page, you would use:

SELECT  *
FROM    mytable
WHERE   (value, id) > (17, 345)
ORDER BY
        value, id
LIMIT 0, 10

, which uses the index on (value, id).

Of course this won't help with arbitrary access pages, but helps with sequential browsing.

Also, MySQL has certain issues with late row lookup. If the columns are indexed, it may be worth trying to rewrite your query like this:

SELECT  *
FROM    (
        SELECT  id
        FROM    mytable
        ORDER BY
                value, id
        LIMIT   $offset, $limit
        ) q
JOIN    mytable m
ON      m.id = q.id

See this article for more detailed explanations:

• MySQL ORDER BY / LIMIT performance: late row lookups