Strange sort behavior on a partitioned table with a prefix index

Question

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?

Answer 1

• 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.)