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

• Paul White: Comments are not for extended discussion; this conversation has been moved to chat.