MyBatis Paging Query

Today, we studied the paging query for MySQL and recorded and shared the following:

Mode 1:

SELECT * FROM table order by ID limit m, n;

The statement means, query m+n records, remove the first m bar, return to the last N records. This query is undoubtedly capable of paging, but if the value of M is larger, the performance of the query will be lower (the lower the number of pages, the lower the query performance), because MySQL also needs to scan the M+n records.

Mode 2:

SELECT * FROM table where ID > #max_id # ORDER by ID limit n;

The query returns n records each time, but does not have to scan the m record like Mode 1, and in the case of large data volumes, performance can be significantly better than mode 1, but the paging query must have a maximum ID (or minimum ID) of the last query (previous page) for each query. The problem with this query is that we sometimes have no way to get the maximum ID (or the minimum ID) of the last query (the previous page), such as the current page 3rd, which needs to query the 5th page of data, the query method is helpless.

Mode 3:

In order to avoid the implementation of the way 2 can not implement the query, you also need to use the limit m, n clauses, in order to performance, it is necessary to the value of M to try to small, such as the current 3rd page, need to query 5th page, 10 data per page, the current 3rd page of the maximum ID is #max_id#:

SELECT * FROM table where ID > #max_id # ORDER by ID limit 20, 10;

In fact, this query method is partially resolved by the problem of mode 2, but if you are currently on page 2nd, you need to query page 100th or 1000, performance will still be poor.

Mode 4:

SELECT * FROM table as a inner joins (SELECT ID from table order by ID of limit m, N) as B on a.id = b.id ORDER by a.id;

This query, like Mode 1, may have a large value for M, but because the internal subquery only scans the field ID instead of the entire table, the performance is better than the way 1 query, and the query resolves issues that are not resolved by mode 2 and Mode 3.

Mode 5:

SELECT * FROM table where ID > (select id from table order by ID limit m, 1) limit n;

The query is in the same way as 4, and also scan the field ID by subquery, the effect is the same way as 4. As for performance, the performance of mode 5 will be slightly better than mode 4, because mode 5 does not need to be associated with a table, but rather a simple comparison.

MyBatis Paging Query