Is there a problem with the limit condition when putting a non-uniqueness field in the order by clause in the MySQL 5.6 version?

Asked 2 years ago, Updated 2 years ago, 93 views

Hello, I'm writing because I have a question about mySQL query. The query is thrown in the form of a bulletin board list, and the query is as follows.

1page

select * from table_name where ~~~~ order by DueDate limit 0,15;

2page

select * from table_name where ~~~~ order by DueDate limit 15,15;

This is a query that shows 15 times for each page, and I am using limit in mysql.

The problem is that the record on page 1 appears again on page 2.(Except for the where and limit conditions, one case is certain.)

I think this phenomenon occurs because the DueDate field applied to order by is not a unique field such as PK or index.

order by DueDate, tableid gave PK to the conditional clause, but it is normal if it is modified like this.

Does this problem occur if a field that does not have uniqueness is placed under the order by condition?

mysql order

2022-09-22 20:33

1 Answers

Rather than the problem caused by not having uniqueness, I think it is caused by the fact that DueDate has multiple values.

For example, if the results selected through a condition (where) are as follows, and you divide the pages by 10 pages,

The first page will contain 1 to 7, but 3 of the 8 to 12 will be sorted first. And then in the case of the page, it's going to appear on the list of 2 out of 3 out of 8 to 12 and 13 to 20 If there are no sub-alignment criteria to determine the order of 8-12 at this time, it may appear duplicated in some cases.


2022-09-22 20:33

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.