It’s normal for Mysql queries with higher offset to be slower than usual. It’s because the Mysql needs to count the OFFSET and LIMIT of your query statement. The higher the offset, the longer Mysql will have to count and the longer the query will run. Check out these two different queries below:

Statement 1.

SELECT * FROM post ORDER BY ID LIMIT 0, 30;

Statement 2.

SELECT * FROM post ORDER BY ID LIMIT 500000, 30;

So if you run the queries above, you will notice that statement 1. is much faster than statement 2.. 🙂

How to fix it

There are 2 methods that I can present to you that is much faster when you’re doing a HIGH OFFSET query statement. Check this out.

Trick 1.

SELECT a.* FROM (SELECT * FROM post ORDER BY ID LIMIT 500000, 30 ) b JOIN a ON a.ID = b.ID;

This query may not be very fast, but at least it’s much faster than the normal query. Please note that the ID column should be a PRIMARY KEY.

Trick 2.

This particular query needs additional step. You need to somehow save the last ID of your previous query result.

Assuming you save your last ID as lastID. Then your query statement should be like this.

SELECT * FROM post WHERE ID > lastID ORDER BY ID LIMIT 30;

This query statement will run much faster than the other queries because it doesn’t have to count the rows during the execution.