MySQL paging performance problem, Limit performance problem to solve!

As we all know, MySQL paging is to use limit for paging, the amount of data/number of pages small when the limit performance is conceivable. Such as:

SELECT pid,author,hash,dateline FROM posts WHERE pid='123456' ORDER BY pid ASC LIMIT 100,100;

There is no problem with the SQL statement performance described above.

However, performance bottlenecks can occur if the offset is too cheap. Such as:

SELECT pid,author,hash,dateline FROM posts WHERE pid='123456' ORDER BY pid ASC LIMIT 159000,100;

To resolve the limit offset too large, you can use the subquery method for paging. Such as:

SELECT pid,author,hash,dateline FROM posts ORDER BY pid ASC LIMIT 159000,100;

Equivalent to:

SELECT pid,author,hash,dateline FROM posts WHERE pid>=(SELECT pid FROM posts LIMIT 159000,1)ORDER BY pid ASC LIMIT 100;

The performance at this time is a few times to the height of the pure limit 159000,100.

But again, the subquery has to be data continuous, so the where query condition cannot be added, such as:

SELECT tid,pid,author,hash,dateline FROM posts WHERE tid=10 AND pid>=(SELECT pid FROM posts LIMIT 159000,1)ORDER BY pid ASC LIMIT 100;

The result is not the result I want at this time.

Therefore, the method of solving the limit paging by high performance is obtained. Online and so on.

============================ps:pid PRIMARY Key

Reply content:

As we all know, MySQL paging is to use limit for paging, the amount of data/number of pages small when the limit performance is conceivable. Such as:

SELECT pid,author,hash,dateline FROM posts WHERE pid='123456' ORDER BY pid ASC LIMIT 100,100;

There is no problem with the SQL statement performance described above.

However, performance bottlenecks can occur if the offset is too cheap. Such as:

SELECT pid,author,hash,dateline FROM posts WHERE pid='123456' ORDER BY pid ASC LIMIT 159000,100;

To resolve the limit offset too large, you can use the subquery method for paging. Such as:

SELECT pid,author,hash,dateline FROM posts ORDER BY pid ASC LIMIT 159000,100;

Equivalent to:

SELECT pid,author,hash,dateline FROM posts WHERE pid>=(SELECT pid FROM posts LIMIT 159000,1)ORDER BY pid ASC LIMIT 100;

The performance at this time is a few times to the height of the pure limit 159000,100.

But again, the subquery has to be data continuous, so the where query condition cannot be added, such as:

SELECT tid,pid,author,hash,dateline FROM posts WHERE tid=10 AND pid>=(SELECT pid FROM posts LIMIT 159000,1)ORDER BY pid ASC LIMIT 100;

The result is not the result I want at this time.

Therefore, the method of solving the limit paging by high performance is obtained. Online and so on.

============================ps:pid PRIMARY Key

This PDF will solve your problem.

Efficient pagination Using MySQL

What is your PID, not the primary key? The last pid=10 and pid>= (SELECT pid from posts LIMIT 159000,1) can it be set up at the same time?

There are conditions outside, the subquery is still written on the line, and sub-query the same conditions, order by the same, such as: SELECT * from the user where sex=2 and issingle=0 and id<= (select ID from user W Here sex=2 and issingle=0 ORDER by id DESC limit 1000000,1) Order BY id DESC limit 10 ";

    ALTER TABLE `posts `  ADD INDEX `tid_pid` USING BTREE (`tid`,`pid`) comment '';

