Implementation of Fast Paging for large data volumes in MySQL

This is generally the case when you start to learn SQL statements.

The Code is as follows:
 
SELECT * FROM table order by id LIMIT 1000, 10;

However, when the data reaches the million level, writing will slow down.

The Code is as follows:
 
SELECT * FROM table order by id LIMIT 1000000, 10;

It may take dozens of seconds.

Many optimization methods on the Internet are as follows:

The Code is as follows:
 
SELECT * FROM table WHERE id> = (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10;

Yes, the speed has been increased to 0. x seconds. It looks okay.
However, it is not perfect!

The following sentence is perfect!

The Code is as follows:
 
SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;

5 to 10 times faster than the above sentence

In addition, if the query id is not a consecutive segment, the best way is to first find the id and then use in to query

The Code is as follows:
 
SELECT * FROM table WHERE id IN (10000,100 000, 1000000 ...);

 

Source: http://www.aichengxu.com/article/MySQL/1093_10.html