Mysql limit paging query optimization statement, mysqllimit

Mysql limit paging query optimization statement, mysqllimit

When querying by page in mysql, limit queries are generally used, and orderby is usually used in queries. However, when the table data volume is large, for example, the query statement segment limit 10000 or 20, the database reads 10020 data records and then discards the first 10000 data records, return the last 20 results to you. This consumption can be avoided and is unnecessary. The following describes several optimization methods:
Optimization Method 1 (for paging operations to be performed in the index ): 
Generally, indexes are created for columns that are frequently used as condition queries. For example
SQL code
SELECT msg_id, msg_content FROM message order by gmt_create desc LIMIT 100, 20;
You can write it 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. When the msg_id of the current page is obtained, the final data details will be obtained through an inner join, this avoids the consumption of operations on a large amount of data details. Of course, the JOIN operation can also be implemented through subqueries. However, the book introduces that mysql versions earlier than mysql 5.6 prefer to use JOIN compared to subqueries.
Optimization Method 2 (explicitly specifying the index column range to be queried)
For example, gmt_create in method 1 is an index column and you know the time range to be queried. Then 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 obtain the desired data through a range query.
Optimization Method 3 (OFFSET is explicitly specified as the query condition)
For example, we can explicitly specify a query time in the query parameter, called lastVisitTime. The following statement can be used to query the first page:
SQL code
SELECT msg_id, msg_content FROM message
Order by gmt_create desc
LIMIT 20
We record the gmt_create field of the last data record in the lastVisitTime field. Then, the query on the back page can be implemented using 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 does not affect the efficiency regardless of the number of pages you query.