Mysql order BY with limit mix trap

In MySQL we often use order by to sort, use the limit to page, when we need to sort after the pagination when we often used a similar wording select * from table name order by sort field LIMT m,n. But this writing hides a deeper use trap. In the case of data duplication in a sorted field, it is easy to get the problem that the sorting result is inconsistent with the expected.

For example, there is now a user table with table structure and data as follows:

Table structure
Table Data

Now you want to query the user table based on the creation time in ascending order, and paged query, each page 2, it is easy to write sql: SELECT * from the user order by Create_time limit pageno,2;

During the execution of the query, you will find:
1, when querying the first page of data:

First page Query results

2. When querying the fourth page data:

Fourth page Query Results

There are 8 data in the user table and 4 pages of data, but the same data appears on the first page and on page fourth in the actual query process.

What's the situation? is not the above paged SQL first two Table association query out, and then order, and then take the corresponding paging data???

The actual implementation results above have shown that there is often a gap between reality and imagination, and that actual SQL execution is not performed in the manner described above. This is actually MySQL will optimize the limit, the specific optimization method see the Official document: https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
This is the 5.7 version of the note that extracts several questions directly related to the point to do the following instructions.

Paste_image.png

As mentioned in the official document above, if you mix limit row_count with order by, MySQL will find the sorted Row_count line and return immediately instead of sorting the entire query result. If it is sorted by index, it will be very fast; If the file is sorted, all rows matching the query (without limit) will be selected, most or all of the selected will be sorted until the row_count of the limit requirement is found. If the Row_count line required by limit is found, MySQL will not sort the remaining rows in the result set.

Here we look at the execution plan for the corresponding SQL:

Paste_image.png

You can confirm that the file is sorted, and that the table does not have an additional index. So we can be sure that this SQL execution will return the results of the query immediately after it finds the line required by the limit.

But even if it returns immediately, why is the pagination not allowed?

The official documentation contains the following instructions:

Paste_image.png

If the order by field has more than one row with the same value, MySQL returns the query result in a random order, depending on the corresponding execution plan. That is, if the sorted column is unordered, the order of the ordered result rows is also indeterminate.

Based on this we basically know why the pagination is not allowed, because we sort of the field is Create_time, just have a few of the same value of the row, the actual execution of the returned results corresponding to the order of the row is indeterminate. For the above case, the first page returns a data row with name 8, which may be exactly in front, and the fourth page query with the row of name 8 is exactly behind, so page fourth appears again.

So how should this be solved?

The government has given the solution:

Paste_image.png

If you want to guarantee that the sort result is the same if the limit exists or does not exist, you can add an additional sort condition. For example, the ID field is unique and you can consider adding an additional ID to the sort field to ensure that the order is stable.

So in the case above, you can add a sort field in SQL, such as the ID field of Fund_flow, so the problem of paging is solved. The modified SQL can look like this:
SELECT * from user ORDER by Create_time,id LIMIT 6,2;

Test problem solving again!!

Mysql order BY with limit mix trap