MySQL large table performance issues when paging with PHP

Question

Have a huge mysql table with like 300,000 records and wanted to page the records in PHP (not the point here though) with a query in this manner:

SELECT * FROM `table` LIMIT 250000, 100 

It could be majorly slow in the latter part of the records, especially when near the end of the table (LIMIT start very large). My guess is MySQL has to count all the way down to exactly 250000 before scooping the results to me?

So how to work around this or any other approach for paging that could be much faster? Thanks!

Answer 1

Make sure you're using an index otherwise it's doing a full table scan. You can look at the execution plan to verify this or force the issue by using an ORDER BY clause (on an indexed column). Here is more information.

Your table isn't that large at 300k rows. There are performance issues with getting near th eend of the table however. The only real solution for this is to to fake the limit clause. Have an auto increment field that numbers the rows from 1 to 300,000 and then do:

SELECT *
FROM mytable
WHERE field BETWEEN 250000 and 250100

or similar. That might be problematic or impossible if you're frequently deleting rows but I tend to find that older data tends to change less so you could somewhat optimize it by using LIMIT for the first 100,000 rows and the surrogate paging column beyond that.

Answer 2

You are correct: MySQL has to scan 250000 useless rows before reading the ones you want. There is really no workaround for this save splitting a table into multiple ones or having a hack such as:

SELECT * FROM table WHERE id BETWEEN 250000 AND 250000 + 100 - 1
;or
SELECT * FROM table WHERE id > 250000 ORDER BY id ASC LIMIT 100

But this still doesn't accurately emulate the function of the LIMIT operator on complex queries. It's a speed:functionality opportunity cost.