MySQL efficient paging solution set sharing

1. The most common paging Method for MYSQL:
Copy codeThe Code is as follows:
Select * from content order by id desc limit 0, 10

In the case of small data volumes, such SQL statements are enough. The only problem that needs to be paid attention is that indexes are used. As the amount of data increases, the number of pages will increase. The SQL statements on the last few pages may be similar:
Copy codeThe Code is as follows:
Select * from content order by id desc limit 10000, 10

In a word, the more paging goes, the larger the offset of the LIMIT statement, and the slower the speed.
In this case, we can use two methods:
1. The paging method of subqueries improves paging efficiency. The SQL statements that are easy to use are as follows:
Copy codeThe Code is as follows:
SELECT * FROM 'content' WHERE id (SELECT id FROM 'content' order by id desc LIMIT ". ($ page-1) * $ pagesize. ", 1) order by id desc LIMIT $ pagesize

Why? Because subqueries are completed on indexes, while normal queries are completed on data files, index files are usually much smaller than data files, therefore, the operation will be more efficient. (Via) The explain SQL statement shows that the subquery uses an index!
Copy codeThe Code is as follows:
Id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY content range PRIMARY 4 NULL 6264 Using where
2 SUBQUERY content index null primary 4 NULL 27085 Using index

After easy measurement, the efficiency of the subquery paging method is 14-20 times higher than that of the pure LIMIT method!
Ii. JOIN paging Mode
Copy codeThe Code is as follows:
Select * FROM 'content' AS t1
JOIN (SELECT id FROM 'content' order by id desc LIMIT ". ($ page-1) * $ pagesize.", 1) AS t2
WHERE t1.id

In my tests, the efficiency of join paging and subquery paging is basically at the same level, and the consumed time is basically the same. Explain SQL statement:
Copy codeThe Code is as follows:
Id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY system NULL 1
1 PRIMARY t1 range PRIMARY 4 NULL 6264 Using where
2 DERIVED content index null primary 4 NULL 27085 Using index

3. Use the FOUND_ROWS () function of MYSQL.
Mysql FOUND_ROWS () function combined with SQL _CALC_FOUND_ROWS can get two results in SELECT:
1. Get the Limit content
2. Get all rows after Limit is removed.
In SELECT statements, LIMIT is often used to LIMIT the number of returned rows. Sometimes you may want to know how many rows will be returned without LIMIT, but you do not want to execute the same statement again. Then, the SELECT query contains the SQL _CALC_FOUND_ROWS option, and then execute FOUND_ROWS:
Copy codeThe Code is as follows:
Select SQL _CALC_FOUND_ROWS * FROM tbl_name WHERE id> 100 LIMIT 10;
SELECT FOUND_ROWS ();

SQL _CALC_FOUND_ROWS tells Mysql to record the number of rows processed by SQL, and FOUND_ROWS () is recorded. Although it is also two statements, but only one master query is executed, so the efficiency is much higher than the original.
1. If the SQL _CALC_FOUND_ROWS option is used in the previous statement, FOUND_ROWS () returns the number of rows returned when the first statement does not have LIMIT.
2. If the SQL _CALC_FOUND_ROWS option is not used in the previous statement, FOUND_ROWS () returns the number of rows actually returned by the previous statement.
If SELECT SQL _CALC_FOUND_ROWS is used, MySQL must calculate the number of rows in all result sets. In this case, it is faster than executing a query that does not use LIMIT again, because the result set will return client drops. (In addition, it should not be because the result set is not returned, but it may be because LIKE and other difficult SQL statements do not need to be exhausted again .)
Copy codeThe Code is as follows:
-- Note that the condition LIKE in the following statement
SELECT SQL _CALC_FOUND_ROWS * FROM tbl_name WHERE Name LIKE '% string %' id> 100 LIMIT 10;
SELECT FOUND_ROWS ();

Copy codeThe Code is as follows:
-- The preceding statement is equivalent to the following statement, but the performance improvement should be very obvious:
Select count (*) FROM tbl_name WHERE Name LIKE '% string % ';
SELECT * FROM tbl_name WHERE Name LIKE '% string %' id> 100 LIMIT 10;