[MDEV-645] LP:1002146 - Unneeded filesort when executing a GROUP BY query Created: 2012-05-21  Updated: 2012-11-23  Resolved: 2012-11-23

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 5.3.11

Type: Bug Priority: Minor
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1002146.xml    

 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

---------------------------------------------------------------------------------------------------------------------+

 Comments   
Comment by Rasmus Johansson (Inactive) [ 2012-05-21 ]

Launchpad bug id: 1002146

Comment by Igor Babaev [ 2012-11-20 ]

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

---------------------------------------------------------------------------------------------------------------------------+

Comment by Igor Babaev [ 2012-11-22 ]

The bug is reproducible in mariadb-5.3 as well.

Comment by Igor Babaev [ 2012-11-23 ]

The fix was pushed into mariadb-5.3 (rev 3604)

Generated at Thu Feb 08 06:30:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.