MySQL Efficient paging solution set

One, the most common MySQL most basic paging way:

SELECT * from content ORDER BY id desc limit 0, 10

In the case of small amounts of data, such SQL is sufficient, and the only problem to be aware of is to ensure that the index is used. As the amount of data increases, the number of pages is increasing, and looking at the next few pages of SQL can be similar:

SELECT * from content ORDER BY id DESC limit 10000, 10

Word, the more backward the page, the offset of the limit statement will be greater, the speed will be significantly slower.
In this case, there are 2 ways to:
One, sub-query paging way to improve paging efficiency, floating easy-to-use SQL statements are 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 is that? Because subqueries are done on the index, and ordinary queries are done on a data file, the index file is generally much smaller than the data file, so it is more efficient to operate. (via) found by explain SQL statement: The subquery uses the index!

ID select_type table Type possible_keys key Key_len ref rows Extra
1 PRIMARY Content Range PRIMARY PRIMARY 4 NULL 6264 Using where
2 subquery content index NULL PRIMARY 4 null 27085 Using Index

Through the floating easy measurement, the use of sub-query paging method of efficiency than the pure limit increased by 14-20 times!
Second, join paging mode

SELECT * from ' content ' as T1
JOIN (SELECT ID from ' content ' ORDER by id desc LIMIT.) ( $page-1) * $pagesize. ", 1) as T2
WHERE t1.id

After my testing, the efficiency of join paging and sub-query paging is basically on one level, and the time consumed is basically the same. Explain SQL statements:

ID select_type table Type possible_keys key Key_len ref rows Extra
1 PRIMARY system null NULL NULL 1
1 PRIMARY T1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 null 27085 Using Index

Third, use MySQL's found_rows () function
The Mysql found_rows () function combined with sql_calc_found_rows can get two results in a select:
1. Get the content of limit
2. Get rid of all lines after limit

It is often possible to return rows with limit restrictions in SELECT statements. Sometimes you may want to know how many rows will be returned without a limit, but you do not want to execute the same statement again. Then, include the Sql_calc_found_rows option in the Select query and then execute Found_rows () to do so:

Select Sql_calc_found_rows * from Tbl_name WHERE ID > LIMIT 10;
SELECT found_rows ();

Where Sql_calc_found_rows tells MySQL to record the number of rows processed by SQL, Found_rows () takes this record. Although it is also two statements, but only once the main query, so the efficiency is much higher than the original.

1. If you use the Sql_calc_found_rows option in the previous statement, Found_rows () returns the number of rows returned when the first statement does not have a 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 you use SELECT Sql_calc_found_rows,mysql, you must calculate the number of rows for all result sets. Even so, it's much faster than executing a query that does not use limit again, because that result set returns to the client drop. (also: there should not be a reason not to return the result set, but it may be that the more laborious SQL such as like does not need to be overworked.) ）

-note the conditions in the following statement like
SELECT sql_calc_found_rows * from tbl_name WHERE name like '%string% ' ID > 10;
SELECT found_rows ();

--The above statement is equivalent to the following statement, but the performance aspect should be improved very significantly:
SELECT COUNT (*) from Tbl_name WHERE name is like '%string% ';
SELECT * from Tbl_name WHERE name like '%string% ' ID > 10 LIMIT;

Transferred from: http://blog.csdn.net/lyd518/article/details/12444153

MySQL Efficient paging solution set