Unneeded filesort when executing a GROUP BY query

Bug #1002146 reported by Igor Babaev on 2012-05-21
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Confirmed
Medium
Igor Babaev

Bug 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 |
+------+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------------------------------------+

Changed in maria:
status: New → Confirmed
importance: Undecided → Medium
assignee: nobody → Igor Babaev (igorb-seattle)
milestone: none → 5.5
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers