This is similar to the following upstream bug that was fixed in MySQL 5.6, but this problem seems to effect all InnoDB tables in MariaDB 10.1, not just partitioned ones:
https://bugs.mysql.com/bug.php?id=60023
For example, run the following test:
CREATE TABLE tbl1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)) ENGINE=InnoDB;
|
INSERT INTO tbl1(a) VALUES (1), (2), (3), (4);
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
INSERT INTO tbl1(a) SELECT a FROM tbl1;
|
|
OPTIMIZE TABLE tbl1;
|
EXPLAIN SELECT DISTINCT a FROM tbl1;
|
SELECT DISTINCT a FROM tbl1;
|
EXPLAIN SELECT a FROM tbl1 GROUP BY a;
|
SELECT a FROM tbl1 GROUP BY a;
|
Here's the output seen on MariaDB 10.1.31:
MariaDB [db1]> EXPLAIN SELECT DISTINCT a FROM tbl1;
|
+------+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
|
| 1 | SIMPLE | tbl1 | index | NULL | a | 4 | NULL | 65895 | Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [db1]> SELECT DISTINCT a FROM tbl1;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
| 4 |
|
+---+
|
4 rows in set (0.02 sec)
|
|
MariaDB [db1]> EXPLAIN SELECT a FROM tbl1 GROUP BY a;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | tbl1 | range | NULL | a | 4 | NULL | 7 | Using index for group-by |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [db1]> SELECT a FROM tbl1 GROUP BY a;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
| 4 |
|
+---+
|
4 rows in set (0.00 sec)
|
Compare this to the output seen on 5.6.38:
mysql> EXPLAIN SELECT DISTINCT a FROM tbl1;
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | tbl1 | range | a | a | 4 | NULL | 7 | Using index for group-by |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
mysql> SELECT DISTINCT a FROM tbl1;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
| 4 |
|
+---+
|
4 rows in set (0.00 sec)
|
|
mysql> EXPLAIN SELECT a FROM tbl1 GROUP BY a;
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | tbl1 | range | a | a | 4 | NULL | 7 | Using index for group-by |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
mysql> SELECT a FROM tbl1 GROUP BY a;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
| 4 |
|
+---+
|
4 rows in set (0.00 sec)
|
Bug
Critical
