How to solve the pitfall of mysql order by limit

Pitfalls encountered when performing paging queries:

Problem discovered: After sorting a single non-indexed field, limit was found. It was found that when the sorted fields have the same value and are within the limit range, the value taken is not the value after normal sorting,

That is to say, when the data ranked in row N can be key1 or key2, the sorting result may be key1 or key2.

Sort limit results (sort key without index)
Press cnt to get the key_word and separate the top three results:

Original text:

If multiple rows have identical values ​​in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.
It means that if the columns of order by have the same value, mysql will randomly select these rows, which will vary according to the execution plan.

Solution: The order by column contains an index column
Add the primary key id here as the sorting column

The above is the detailed content of How to solve the pitfall of mysql order by limit. For more information, please follow other related articles on the PHP Chinese website!