Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve limit paging query in mysql, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Optimization method 1 (let the paging operation occur in the index):

In general, the columns in a table that are often used as conditional queries are indexed, such as the following query

Sql code

SELECT msg_id, msg_content FROM message ORDER BY gmt_create desc LIMIT 100, 20

It can be written as follows

Sql code

SELECT msg_id, msg_content FROM message

INNER JOIN (

SELECT msg_id FROM message

ORDER BY gmt_create LIMIT 100, 20

) AS page USING (msg_id)

In this way, the content of the current query page will only be carried out in the index, and when you get the msg_id of the current page, you can get the final data details through an INNER JOIN, which avoids the consumption of operating a large number of data details. Of course, JOIN operations can also be implemented through subqueries, but it is introduced in the book that versions of mysql prior to 5.6 still prefer JOIN to subqueries.

Optimization method 2 (explicitly specify the range of index columns to query)

For example, gmt_create in method 1 is the column that builds the index, and you also know the time range to query, so you can use the following query statement:

Sql code

SELECT msg_id, msg_content FROM message

WHERE gmt_create BETWEEN # startTime# AND # endTime#

ORDER BY gmt_create desc

In this way, the database can get the desired data through a range query.

Optimization method 3 (OFFSET is explicitly specified as a query condition)

As the example above, we can explicitly specify a query time in the query parameters, which is called lastVisitTime. We can query the first page with the following statement:

Sql code

SELECT msg_id, msg_content FROM message

ORDER BY gmt_create desc

LIMIT 20

If we record the gmt_create field of the last piece of data read out in the lastVisitTime field, the query on the following page can be implemented with the following statement:

Sql code

SELECT msg_id, msg_content FROM message

WHERE gmt_create < # lastVisitTime#

ORDER BY gmt_create desc

LIMIT 20

This query method, no matter how many pages you query, paging will not be a factor affecting efficiency.

The above is how to implement limit paging query in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.