MySQL order by sorting result is incorrect
MySQL order by sorting result is incorrect
Create a new test table:
CREATE TABLE `tb1` (
`id` bigint (20) NOT NULL AUTO_INCREMENT
`a` decimal (19pr 2) NOT NULL
`acid` bigint (20) NOT NULL
`prid` bigint (20) NOT NULL
PRIMARY KEY (`id`)
KEY `idx_ prid` (`prid`)
KEY `idx_ acid` (`acid`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
Field a has no index, insert test data:
INSERT INTO `tb1` (`id`, `a`, `acid`, `prid`)
VALUES (1pyrrine 2.00pyrr3), (2pjor3.00pyr2), (3pjre 4.00pyr2), (4pjor5.00pyr2), (5pyrrp2), (6pr 8.00pyrr2), (7ml10.00pyr2), (8pr 12.00pyr2), (9pr 16.00 2pr 3), (10jie 20.00 2pr 3), (11pr 6.00pyrus 2pr 4), (12jue 8.00pyrrine 2pen4), (13jue 10.00pr 4), (14rec 12.00pr 2pr 4), (15pr 5.00pr 2pr 2), (166.00 pr 2), (166.00 pr 2)
View table data:
([yoon] > select * from tb1
+-- +
| | id | a | acid | prid | |
+-- +
| | 1 | 2.00 | 3 | 2 | |
| | 2 | 3.00 | 3 | 2 |
| | 3 | 4.00 | 2 | 3 |
| | 4 | 5.00 | 2 | 3 | |
| | 5 | 6.00 | 2 | 3 |
| | 6 | 8.00 | 2 | 3 |
| | 7 | 10.00 | 2 | 3 | |
| | 8 | 12.00 | 2 | 3 | |
| | 9 | 16.00 | 2 | 3 |
| | 10 | 20.00 | 2 | 3 | |
| | 11 | 6.00 | 2 | 4 |
| | 12 | 8.00 | 2 | 4 |
| | 13 | 10.00 | 2 | 4 | |
| | 14 | 12.00 | 2 | 4 |
| | 15 | 5.00 | 2 | 2 |
| | 16 | 6.00 | 2 | 2 |
+-- +
Order by sort by field a that is not an indexed field and has duplicate data:
([yoon] > select * from tb1 order by a desc
+-- +
| | id | a | acid | prid | |
+-- +
| | 10 | 20.00 | 2 | 3 | |
| | 9 | 16.00 | 2 | 3 |
| | 14 | 12.00 | 2 | 4 |
| | 8 | 12.00 | 2 | 3 | |
| | 13 | 10.00 | 2 | 4 | |
| | 7 | 10.00 | 2 | 3 | |
| | 12 | 8.00 | 2 | 4 |
| | 6 | 8.00 | 2 | 3 |
| | 11 | 6.00 | 2 | 4 |
| | 16 | 6.00 | 2 | 2 |
| | 5 | 6.00 | 2 | 3 |
| | 4 | 5.00 | 2 | 3 | |
| | 15 | 5.00 | 2 | 2 |
| | 3 | 4.00 | 2 | 3 |
| | 2 | 3.00 | 3 | 2 |
| | 1 | 2.00 | 3 | 2 | |
+-- +
Order by is used with limit:
([yoon] > select * from tb1 order by a desc limit 4
+-- +
| | id | a | acid | prid | |
+-- +
| | 10 | 20.00 | 2 | 3 | |
| | 9 | 16.00 | 2 | 3 |
| | 14 | 12.00 | 2 | 4 |
| | 8 | 12.00 | 2 | 3 | |
+-- +
Add an index to the a field:
([yoon] > alter table tb1 add index idx_a (a)
Query OK, 0 rows affected (0.05 sec)
([yoon] > select * from tb1 order by a desc limit 4
+-- +
| | id | a | acid | prid | |
+-- +
| | 10 | 20.00 | 2 | 3 | |
| | 9 | 16.00 | 2 | 3 |
| | 14 | 12.00 | 2 | 4 |
| | 8 | 12.00 | 2 | 3 | |
+-- +
Add another field, id, when sorting:
([yoon] > select * from tb1 order by a desc,id desc limit 4
+-- +
| | id | a | acid | prid | |
+-- +
| | 10 | 20.00 | 2 | 3 | |
| | 9 | 16.00 | 2 | 3 |
| | 14 | 12.00 | 2 | 4 |
| | 8 | 12.00 | 2 | 3 | |
+-- +
For a non-unique field, the result set is uncertain regardless of whether it contains an index or not. If the business logic has high requirements for paging or order by result sets, remember to sort with a unique key.
