[MDEV-9589] Optimizer doesn't remove unneeded joins resulting in unneccesary long querytime Created: 2016-02-19  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.11
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: sjon Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: optimizer
Environment:

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


Generated at Thu Feb 08 07:35:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.