Mysql limit usage and several paging forms

1. Mysql limit usage

When we use query statements, we often need to return the first few or middle rows of data. What should we do at this time? Don't worry, mysql already provides us with such a function.

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

The LIMIT clause can be used to force a SELECT statement to return a specified number of records. LIMIT accepts one or two numeric arguments. The parameter must be an integer constant. If two parameters are given, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. The offset of the initial record row is 0 (instead of 1): For compatibility with PostgreSQL, MySQL also supports the syntax: LIMIT # OFFSET #.

mysql> SELECT * FROM table LIMIT 5,10; //Retrieve record rows 6-15

//In order to retrieve all record rows from a certain offset to the end of the record set , you can specify the second parameter to be -1:

mysql> SELECT * FROM table LIMIT 95,-1; // Retrieve record row 96-last.

//If only given A parameter, which indicates the maximum number of record rows to return:

mysql> SELECT * FROM table LIMIT 5; //Retrieve the first 5 record rows

//Replace In other words, LIMIT n is equivalent to LIMIT 0,n.

[Quote, Passerby B: Detailed explanation of the usage of limit in Mysql]

2. Performance analysis of Mysql paging query statement

MySql paging sql statement, if compared with MSSQL's TOP syntax, then MySQL's LIMIT syntax is much more elegant. Using it for pagination is natural.

2.1 The most basic paging method:

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

In the case of small and medium data volumes, this SQL is enough. The only issue that needs attention is to ensure that an index is used:

For example, if the actual SQL is similar to the following statement, then it is better to establish a composite index on the category_id and id columns:

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10

2.2 Paging method of subquery:

As the amount of data increases, the number of pages will become more and more. It is possible to view the SQL of the next few pages Similar:

Sql code Favorite code

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

In a nutshell, the further the page is paged, the greater the offset of the LIMIT statement will be , the speed will also be significantly slower.

At this time, we can improve the paging efficiency through subqueries, roughly as follows:

SELECT * FROM articles WHERE  id >=  
 (SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10

2.3JOIN paging method

SELECT * FROM `content` AS t1   
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

After me According to the test, the efficiency of join paging and subquery paging are basically at the same level, and the time consumed is basically the same.

explain SQL statement:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL 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
----------------------------------------

Why is this? Because the subquery is completed on the index, and the ordinary query is completed on the data file, generally speaking, the index file is much smaller than the data file, so the operation will be more efficient.

Actually, you can use a method similar to the strategy mode to handle paging. For example, if it is determined to be within one hundred pages, use the most basic paging method. If it is greater than one hundred pages, use a subquery. paging method.

The above is the content of Mysql limit usage and several paging forms. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!