Detailed explanation of MySQL limit usage and performance analysis of paging query statements

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 such a function for us.

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 argument must be an integer constant. If two arguments are given, the first argument specifies偏移量of the first returned row, and the second argument specifies the maximum number of returned rows.初始記錄行的偏移量是0(而不是1) : For compatibility with PostgreSQL, MySQL also supports the syntax: LIMIT # OFFSET #.

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

To retrieve all rows from a certain offset to the end of the recordset, you can specify the second parameter as -1:

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

If only one argument is given, it indicates the maximum number of rows to return:

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

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

Performance analysis of MySQL paging query statements

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

The most basic paging method:

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

In the case of small and medium data volumes, such SQL is sufficient. The only thing to note is to ensure that the index is used. For example, if the actual SQL is similar to the following statement, it is better to create a composite index on the category_id and id columns:

Subquery paging method:

As the amount of data increases, the number of pages will increase, and the SQL for the next few pages may be similar to:

In a nutshell, the further you paginate, LIMIT語句的偏移量就會越大，速度也會明顯變慢.

At this point, we can improve the paging efficiency by using subqueries, as follows:

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

JOIN 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;

According to my tests, the efficiency of join paging and subquery paging is 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 happening? Because subqueries are performed on indexes, while ordinary queries are performed on data files, generally speaking, index files are much smaller than data files, so operations will be more efficient.

In fact, you can use a similar strategy pattern to handle paging. For example, if the number of pages is less than 100, use the most basic paging method. If the number of pages is greater than 100, use the subquery paging method.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.