Mysql limit paging Query Optimization

Mysql limit paging query optimization is generally used for paging query in mysql, and orderby is usually used for sorting 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 in tables. For example, SELECT msg_id for the following SQL query code, msg_content FROM message order by gmt_create desc LIMIT 100, 20; you can write the following 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 is only in the index, when the msg_id of the current page is obtained, an inner join is used to obtain the final data details, which 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 the index column, and you know the time range to be queried, in this way, 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 so that the database can obtain the desired data through a range query. In the example above, we can explicitly specify a query time in the query parameter (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 efficiency regardless of the number of pages you query.