Strange sort behavior on a partitioned table with a prefix index 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&gt; SELECT id FROM test WHERE data = 'ab' ORDER BY id ASC;
+----+
| id |
+----+
|  4 |
|  5 |
| 14 |
| 15 |
| 16 |
|  6 |
+----+

mysql&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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?
Do not use index prefixing; it is often more harmful than useful.  Especially for short string.


In a composite index, the Optimizer won't go beyond the prefix index.
Prefixing is somewhat useful when you can't make a full sized index.

Do not use PARTITION unless there is a good reason for it; your schema an queries do not show any use for partitioning.


This lists the only use cases I know of.

Do have INDEX(data, id).


Even without partitioning, this would be the best index for that SELECT.

Do have an explicit PRIMARY KEY on every InnoDB table.


(If this was a watered down version of the real schema, start a new Question without the simplifications.  There may be something useful to discuss.)