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 '';