Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.31
Description
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)
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
- links to