Solve the paging performance problem of MySQL and solve the Limit performance problem!

As we all know, Mysql paging requires the use of Limit for paging. When the data size is small, the Limit performance can be imagined. For example: {code...} the preceding SQL statement has no performance problems. However, if the offset is too low, a performance bottleneck may occur. For example, {code... as we all know, Mysql uses Limit for paging. When the data volume/Page Shard is small, the Limit performance can be imagined. For example:

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

The performance of the preceding SQL statements is normal.

However, if the offset is too low, a performance bottleneck may occur. For example:

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

If you want to solve the problem of excessive Limit offset, you can use the subquery Method for paging. For example:

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

It is equivalent:

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

At this time, the performance is several times higher than that of pure LIMIT 159000,100.

However, the problem arises again, and the subquery must be continuous, so the WHERE query conditions cannot be added, for example:

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 what I want.

Therefore, we need to solve the LIMIT paging problem with high performance. Online.

========================================== PS: pid is the primary key

Reply content:

As we all know, Mysql paging requires the use of Limit for paging. When the data volume/Page Shard is small, the Limit performance can be imagined. For example:

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

The performance of the preceding SQL statements is normal.

However, if the offset is too low, a performance bottleneck may occur. For example:

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

If you want to solve the problem of excessive Limit offset, you can use the subquery Method for paging. For example:

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

It is equivalent:

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

At this time, the performance is several times higher than that of pure LIMIT 159000,100.

However, the problem arises again, and the subquery must be continuous, so the WHERE query conditions cannot be added, for example:

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 what I want.

Therefore, we need to solve the LIMIT paging problem with high performance. Online.

========================================== PS: pid is the primary key

This PDF can solve your problem.

Efficient Pagination Using MySQL

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

If there are some conditions outside, the subquery can still be written in the same way as the subquery condition, and the order by condition is the same, for example: select * from user where sex = 2 and issingle = 0 and id <= (select id from user where sex = 2 and issingle = 0 order by id desc limit 00,1) order by id desc limit 10 ";

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