MySQL Paging optimization

MySQL uses LIMIT offset, length for paging. However, when the number of table records is large, you will find that the query time for large pages is significantly larger than the query time for small pages.

MySQL does not skip the offset line, but instead takes the Offset+n line, then returns the first offset line, returns N rows, and when offset is particularly large, the efficiency is very low.

Workaround One: Limit the total number of pages in the business.

Solution two: With the help of index, quickly locate.

The second way is more commonly used.

Example: The number of Device_version_stat table records is large, and if you need to export the table's data to Excel, subsequent data queries will be relatively slow. If you are descending by ID, you can quickly navigate to the query scope by using the ID primary key.

The first page of Maxid = null, the second page of Maxid = a specific value of 1, the second page of Maxid = a specific value of 2, ...

Queries with the following conditions, query efficiency is much higher,

<if test= "@[email protected" (MiniD) ">
and ID > #{minid}
</if>
<if test= "@[email protected" (MAXID) ">
and ID &lt; #{maxid}
</if>

MySQL Paging optimization