Some additional observations:
- Problem appears to persist after restart (on my local VM running 5.7.11).
- Results are ordered correctly if the index columns are swapped (i.e. (`id`, `data`(2)) instead of (`data`(2), `id`).
- Looks like the values sorted in a partition are cut off at the part where a row appears with a different `data` value (but with the same part in the index).
Test values:
INSERT INTO `sorting` VALUES
/* p10 */
(1,'aa2'),
(2,'aa2'),
(3,'aa2'),
/* p20 */
(11,'aa2'),
(12,'aa2'),
(13,'aa2')
;
Introduce the following 'splitting point' values:
INSERT INTO `sorting` VALUES
/* Break ASC order */
(2,'aa3'),
/* Break DESC order */
(12,'aa1')
;
For the ascending order, MySQL sorts both partitions, and then appends the sorted p456 to p123. Insert a row in between the values of p123 with a greater alphabetical value will result in the rest of the partition being ordered by that greater alphabetical value.
Add an insert to make ordering more clear:
INSERT INTO `sorting` VALUES (4,'aa2'), (5, 'aa2');
Some additional observations:
- Problem appears to persist after restart (on my local VM running 5.7.11).
- Results are ordered correctly if the index columns are swapped (i.e. (`id`, `data`(2)) instead of (`data`(2), `id`).
- Looks like the values sorted in a partition are cut off at the part where a row appears with a different `data` value (but with the same part in the index).
Test values:
INSERT INTO `sorting` VALUES
/* p10 */
(1,'aa2'),
(2,'aa2'),
(3,'aa2'),
/* p20 */
(11,'aa2'),
(12,'aa2'),
(13,'aa2')
;
Introduce the following 'splitting point' values:
INSERT INTO `sorting` VALUES
/* Break ASC order */
(2,'aa3'),
/* Break DESC order */
(12,'aa1')
;
For the ascending order, MySQL sorts both partitions, and then appends the sorted p456 to p123. Insert a row in between the values of p123 with a greater alphabetical value will result in the rest of the partition being ordered by that greater alphabetical value.
Add an insert to make ordering more clear:
INSERT INTO `sorting` VALUES (4,'aa2'), (5, 'aa2');
+----+-----+
| 1 | aa2 |
| 2 | aa2 |
| 2 | aa3 | <-- Partition order splits here, all greater id values will be appended below p20
| 3 | aa2 |
| 4 | aa2 |
| 5 | aa2 |
+----+-----+
| 11 | aa2 |
| 12 | aa2 |
| 13 | aa2 |
+----+-----+
Resulting rows:
mysql> SELECT id FROM sorting WHERE data = 'aa2' ORDER BY id;
+----+
| id |
+----+
| 1 |
| 2 |
| 11 |
| 12 |
| 13 |
| 3 |
| 4 |
| 5 |
+----+
Same thing works for descending order and alphabetical ordering reversed.