Details
Minor
Description
If to create tables t1 and t2 in MariaDB 5.5 as with the following commands
CREATE TABLE t1 (
col_int_key INT,
pk INT,
PRIMARY KEY (pk),
KEY (col_int_key)
) ENGINE=INNODB;
INSERT INTO t1 VALUES (2,3),(3,2),(3,5),(4,6);
CREATE TABLE t2 (
col_int_key INT,
pk INT,
PRIMARY KEY (pk),
KEY (col_int_key)
) ENGINE=INNODB;
INSERT INTO t2 VALUES (0,9),(3,10),(4,6),(6,1),(100,3),(200,5);
and
set join_cache_level=0;
then the execution plan for the query
SELECT t2.col_int_key AS field1
FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
will use unneeded filesort:
MariaDB [test]> explain SELECT t2.col_int_key AS field1 FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.t_key WHERE t2.pk < 7 AND t2.col_int_key <> 7 GROUP BY field1;
---------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t2 | range | col_int_key | col_int_key | 5 | NULL | 5 | Using where; Usinx; Using temporary; Using filesort |
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 4 | Using index |
---------------------------------------------------------------------------------------------------------------------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The same problem can be observed on mariadb-5.5 (rev 3574)with the query
SELECT t2.col_int_key AS field1
FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.col_int_key <> 7
GROUP BY field1;
when the engine is changed for MYISAM:
MariaDB [test]> ALTER TABLE t1 ENGINE=MYISAM;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [test]> ALTER TABLE t2 ENGINE=MYISAM;
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [test]> explain SELECT t2.col_int_key AS field1 FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key WHERE t2.col_int_key <> 7 GROUP BY field1;
---------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t2 | index | col_int_key | col_int_key | 5 | NULL | 6 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 4 | Using index |
---------------------------------------------------------------------------------------------------------------------------+
The bug is reproducible in mariadb-5.3 as well.
The fix was pushed into mariadb-5.3 (rev 3604)
Launchpad bug id: 1002146