In web development, it is common to be able to perform some action on each row of a high volume table. The first thing that comes to mind in doing this is fetching rows from the table in batches using
LIMIT OFFSET,BATCH statement.
SELECT * FROM `table` LIMIT OFFSET,BATCH;
The flaw of this method is as the offset goes on increasing, it takes that much longer for the database engine to return the result. The reason being the engine has to go though all the rows to find that offset and then return the batch of records.
The solution would be to use a where clause on the primary key instead of offset:
SELECT * FROM `table` WHERE `id` > OFFSET ORDER BY `id` LIMIT BATCH;
The ORDER BY clause can get confusing as it usually slows the query but in this case we are ordering a primary key which is uniquely indexed which will be fast.
A huge improvement on the performance and lesser load on the database server.