MySQL Paging query syntax

One, limit syntax

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

the LIMIT clause can be used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. parameter must be an integer constant. Given two parameters, the first parameter specifies the first one to return the record row 偏移量 , and the second parameter specifies the maximum number of rows to return.

初始记录行的偏移量是 0(而不是 1);

mysql>  SELECT  *  FROM  table  LIMIT 5,10; // 检索记录行 6-15

In order to retrieve all the record rows from an offset to the end of the recordset, you can specify a second parameter of-1:

mysql>  SELECT  *  FROM  table  LIMIT 95,-1; // 检索记录行 96- last .

If only one parameter is given, it indicates the maximum number of record rows returned:

mysql>  SELECT *  FROM table LIMIT 5; //检索前 5 个记录行

In other words, LIMIT n equivalent to  LIMIT 0,n .

Second, the performance analysis of MySQL paged query statement The most basic way to page paging:

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

In the case of small amounts of data, such SQL is sufficient, the only problem to be aware of is to ensure that the index is used: for example, if the actual SQL is similar to the following statement, it is better to build a composite index on the ID two column in category_id:

The code is as follows:

SELECT * from articles WHERE category_id = 123 ORDER by ID LIMIT ten

Sub-Query Paging method:

As the amount of data increases, the number of pages is increasing, and looking at the next few pages of SQL can be similar:

The code is as follows:

SELECT * from articles WHERE category_id = 123 ORDER by ID LIMIT 10000,

Word, that is, the more the next pagination, LIMIT语句的偏移量就会越大，速度也会明显变慢 .

At this point, we can improve paging efficiency by sub-query, roughly as follows:

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

Join page 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 <= t2.id  ORDER BY t1.id  desc LIMIT $pagesize;

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  PRIMARY <derived2> system  NULL NULL NULL NULL 1  PRIMARY t1 range  PRIMARY PRIMARY 4  NULL 6264 Using  where  DERIVED content  index NULL PRIMARY 4  NULL 27085 Using  index
then use the subquery statement directly!

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.

You can actually use a similar strategy mode to deal with paging, such as: judging if it is within 100 pages, using the most basic paging method, more than 100 pages, then use the sub-query paging.

MySQL Paging query syntax