Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 5.5.24, 5.3.7, 5.2.12
-
Fix Version/s: 5.5
-
Component/s: None
-
Labels:None
Description
The following query
SELECT DISTINCT alias1.a + alias3.a, CONCAT( alias1.c, alias3.c ), CONCAT( alias1.c, alias1.d ), alias1.* FROM t1 AS alias1, t2 AS alias2, t1 AS alias3, t1 AS alias4 WHERE alias1.b >= alias2.e
takes several times longer when it's executed with the default optimizer_prune_level=1 than with optimizer_prune_level=0.
Reproducible on maria/5.2 revno 3139, maria/5.3 revno 3539, maria/5.5 revno 3426. maria/5.5 shows a bigger difference than 5.2 and 5.3 (it's probably not worth fixing on 5.2 or 5.3 anyway).
The test case uses InnoDB because the problem is most noticeable this way (~6 times slower), although it's reproducible with MyISAM and Aria too (~3 times slower).
Reproducible with the default optimizer_switch as well as with all OFF values.
EXPLAIN on 5.5 with all OFF values, optimizer_prune_level=0:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias1 ALL b NULL NULL NULL 20 100.00 Using temporary 1 SIMPLE alias2 index e e 4 NULL 20 100.00 Using where; Using index; Using join buffer (flat, BNL join) 1 SIMPLE alias4 index NULL b 4 NULL 20 100.00 Using index; Using join buffer (flat, BNL join) 1 SIMPLE alias3 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select distinct (`test`.`alias1`.`a` + `test`.`alias3`.`a`) AS `alias1.a + alias3.a`,concat(`test`.`alias1`.`c`,`test`.`alias3`.`c`) AS `CONCAT( alias1.c, alias3.c )`,concat(`test`.`alias1`.`c`,`test`.`alias1`.`d`) AS `CONCAT( alias1.c, alias1.d )`,`test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias1`.`c` AS `c`,`test`.`alias1`.`d` AS `d` from `test`.`t1` `alias1` join `test`.`t2` `alias2` join `test`.`t1` `alias3` join `test`.`t1` `alias4` where (`test`.`alias1`.`b` >= `test`.`alias2`.`e`)
EXPLAIN on 5.5 with all OFF values, optimizer_prune_level=1:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE alias3 ALL NULL NULL NULL NULL 20 100.00 Using temporary 1 SIMPLE alias4 index NULL b 4 NULL 20 100.00 Using index; Using join buffer (flat, BNL join) 1 SIMPLE alias2 index e e 4 NULL 20 100.00 Using index; Using join buffer (flat, BNL join) 1 SIMPLE alias1 ALL b NULL NULL NULL 20 100.00 Range checked for each record (index map: 0x1) Warnings: Note 1003 select distinct (`test`.`alias1`.`a` + `test`.`alias3`.`a`) AS `alias1.a + alias3.a`,concat(`test`.`alias1`.`c`,`test`.`alias3`.`c`) AS `CONCAT( alias1.c, alias3.c )`,concat(`test`.`alias1`.`c`,`test`.`alias1`.`d`) AS `CONCAT( alias1.c, alias1.d )`,`test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias1`.`c` AS `c`,`test`.`alias1`.`d` AS `d` from `test`.`t1` `alias1` join `test`.`t2` `alias2` join `test`.`t1` `alias3` join `test`.`t1` `alias4` where (`test`.`alias1`.`b` >= `test`.`alias2`.`e`)
Test case:
--source include/have_innodb.inc CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, c VARCHAR(1) NOT NULL, d VARCHAR(1) NOT NULL, KEY (b) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (7,1,'v','v'),(0,7,'s','s'),(9,4,'l','l'),(3,7,'y','y'), (4,0,'c','c'),(2,2,'i','i'),(5,9,'h','h'),(3,4,'q','q'), (1,0,'a','a'),(3,9,'v','v'),(6,1,'u','u'),(7,3,'s','s'), (5,8,'y','y'),(1,8,'z','z'),(4,8,'h','h'),(2,8,'p','p'), (9,6,'e','e'),(5,3,'i','i'),(0,6,'y','y'),(3,6,'w','w'); CREATE TABLE t2 (e INT NOT NULL, KEY (e)) ENGINE=InnoDB; INSERT INTO t2 VALUES (4),(6),(3),( 5),(3),(246),(2),(9),(3),(8), (1),(8),(8),(5),(7),(5),(1),(6),(2),(9); --start_timer SELECT DISTINCT alias1.a + alias3.a, CONCAT( alias1.c, alias3.c ), CONCAT( alias1.c, alias1.d ), alias1.* FROM t1 AS alias1, t2 AS alias2, t1 AS alias3, t1 AS alias4 WHERE alias1.b >= alias2.e;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions