Edit: Filed bug report at MySQL: link
I'm getting strange ordering behavior from MySQL when trying to order a partitioned table with a prefix index.
Problem can be reduced to the following dataset:
CREATE TABLE `test` (
`id` int unsigned NOT NULL,
`data` varchar(2) DEFAULT NULL,
KEY `data_idx` (`data`(1),`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p10 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN (20) ENGINE = InnoDB) */;
INSERT INTO `test` VALUES
(6, 'ab'),
(4, 'ab'),
(5, 'ab'),
(16, 'ab'),
(14, 'ab'),
(15, 'ab'),
/*The following values cause the weird behavior*/
(5, 'ac'),
(15, 'aa')
;
Trying to order on id
with a search on data
gives the following:
mysql> SELECT id FROM test WHERE data = 'ab' ORDER BY id ASC;
+----+
| id |
+----+
| 4 |
| 5 |
| 14 |
| 15 |
| 16 |
| 6 |
+----+
mysql> SELECT id FROM test WHERE data = 'ab' ORDER BY id DESC;
+----+
| id |
+----+
| 16 |
| 6 |
| 5 |
| 4 |
| 15 |
| 14 |
+----+
Tried on both 5.6 and 5.7, with the same results.
Funnily enough, removing the ORDER BY
gets me a neatly ordered result set:
mysql> SELECT id FROM test WHERE data = 'ab';
+----+
| id |
+----+
| 4 |
| 5 |
| 6 |
| 14 |
| 15 |
| 16 |
+----+
Running an EXPLAIN
doesn't appear to yield me anything interesting either:
mysql> EXPLAIN SELECT id FROM test WHERE data = 'ab';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | p10,p20 | ref | data_idx | data_idx | 6 | const | 8 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
mysql> EXPLAIN SELECT id FROM test WHERE data = 'ab' ORDER BY id ASC;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | p10,p20 | ref | data_idx | data_idx | 6 | const | 8 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
mysql> EXPLAIN SELECT id FROM test WHERE data = 'ab' ORDER BY id DESC;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | p10,p20 | ref | data_idx | data_idx | 6 | const | 8 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
Any ideas?