Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.11
-
Archlinux
Description
Our application generates a query which contains a significant amount of joins that the optimizer is able to successfully transform to a relatively simple query. However, this query (still) takes a very long time to complete.
It seems to me the optimizer could do better.
I've created a simple testcase that reproduces the issue (based on the query the optimizer created):
CREATE TABLE `a` ( |
`a` int(11) NOT NULL, |
`b` int(11) NOT NULL, |
PRIMARY KEY (`a`), |
KEY `b` (`b`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
 |
CREATE TABLE `b` ( |
`b` int(11) NOT NULL, |
`c` int(11) NOT NULL, |
KEY `b` (`b`) USING BTREE, |
CONSTRAINT `b_ibfk_1` FOREIGN KEY (`b`) REFERENCES `a` (`b`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
INSERT INTO `a` VALUES (1,1); |
INSERT INTO `b` VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),(1,14); |
and the query:
select a.* from a |
left join b b1 on (b1.b = a.b) |
left join b b2 on (b2.b = a.b) |
left join b b3 on (b3.b = a.b) |
left join b b4 on (b4.b = a.b) |
left join b b5 on (b5.b = a.b) |
left join b b6 on (b6.b = a.b) |
left join b b7 on (b7.b = a.b) |
left join b b8 on (b8.b = a.b) |
left join b b9 on (b9.b = a.b) |
left join b b10 on (b10.b = a.b) |
where a.a=1 |
group by a.a |