Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5.24, 5.3.7, 5.2.12
-
None
-
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;
|