Avoiding offset on tables with high volume data

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: Read More