One, the most common MySQL most basic way to paging:
Copy Code code as follows:
SELECT * from content ORDER BY id desc limit 0, 10
In the case of small and medium data, such SQL is sufficient, 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 becomes more numerous, and the next few pages of SQL may look similar:
Copy Code code as follows:
SELECT * from content ORDER BY id DESC limit 10000, 10
Word, is the more backward page, the limit statement will be the greater the offset, the speed will be significantly slower.
At this point, we can go through 2 different ways:
First, the Sub-query page to improve paging efficiency, floating in the use of SQL statements are as follows:
Copy Code code 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 the subquery is done on the index, and the normal query is done on the data file, the index file is usually much smaller than the data file, so it will be more efficient to operate. (via) found through explain SQL statements: Subqueries Use the index!
Copy Code code as follows:
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
After drifting easily measured, the use of the sub-page method of paging efficiency than pure limit increased by 14-20 times!
Second, join paging way
Copy Code code 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
After my test, the efficiency of join paging and subquery paging is basically one level, and the time consumed is basically the same. Explain SQL statement:
Copy Code code as follows:
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 the MySQL found_rows () function
The Mysql found_rows () function combines sql_calc_found_rows to get two results in a select:
1. Get the content of limit
2. Get rid of all the lines after limit
In a SELECT statement, it is often possible to return the number of rows with limit restrictions. Sometimes you might want to know how many rows are returned without limit, but you don't want to execute the same statement again. Then it's OK to include the sql_calc_found_rows option in the Select query, and then execute Found_rows ():
Copy Code code as follows:
Select Sql_calc_found_rows * from Tbl_name WHERE ID > LIMIT 10;
SELECT found_rows ();
Where Sql_calc_found_rows told MySQL to record the number of rows the SQL processed, Found_rows () took the 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 has no limit.
2. If the Sql_calc_found_rows option is not used in the previous statement, Found_rows () returns the actual number of rows 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 a lot quicker than executing a query that doesn't use limit, because then the result set returns to the client drop. (also: there should be more than a reason not to return the result set, and there may be reasons such as like, such as more laborious SQL does not need to be tired again.) ）
Copy Code code as follows:
--note the conditions in the following statement like
SELECT sql_calc_found_rows * from tbl_name WHERE name like '%string% ' ID > LIMIT 10;
SELECT found_rows ();
Copy Code code as follows:
-The above statement is equivalent to the following statement, but the performance aspect should be elevated very, very clearly:
SELECT COUNT (*) from Tbl_name WHERE name is like '%string% ';
SELECT * from Tbl_name WHERE name like '%string% ' ID > LIMIT 10;
