[MDEV-6724] Slow query optimizer with certain subqueries Created: 2014-09-10  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5.33a, 10.0.14, 10.1.0
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Ales Havlik (Inactive) Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: optimizer, semi-join, subquery
Environment:

Centos 6.4 x64

Attachments: File slow_query.sql    
Issue Links:
Duplicate
duplicates MDEV-6407 Incredibly slow performance on SELECT... Stalled
Relates
relates to MDEV-7275 Table elimination is not used for tab... Open

 Description   

Hi,
I recently found a query which takes hours to execute on MariaDB 5.5. I had same result with MariaDB 10.1.0. When using MySQL 5.6.20 or 5.1.73 the execution is instant.

I tried to simplify query and this is the result. Data to run it on attached:

explain select *
 
from   JobTaskTimeEntry this_ 
where  this_.id in (select this_.id as y0_ 
                        from   JobTaskTimeEntry this_ 
                               left outer join JobTask jobtask3_ 
                                            on this_.JobTaskId = jobtask3_.id 
                               left outer join Job jobtask_jo1_ 
                                            on jobtask3_.JobId = jobtask_jo1_.id 
                               left outer join JobFreeTagLink jobtask_jo2_ 
                                            on jobtask3_.JobId = 
                                               jobtask_jo2_.JobId 
                        where  jobtask3_.JobId = 1) 
       and this_.id in (select this_.id as y0_ 
                        from   JobTaskTimeEntry this_ 
                               left outer join JobTask jobtask3_ 
                                            on this_.JobTaskId = jobtask3_.id 
                               left outer join Job jobtask_jo1_ 
                                            on jobtask3_.JobId = jobtask_jo1_.id 
                               left outer join JobFreeTagLink jobtask_jo2_ 
                                            on jobtask3_.JobId = 
                                               jobtask_jo2_.JobId 
                        where  jobtask3_.JobId = 2) 
       and this_.id in (select this_.id as y0_ 
                        from   JobTaskTimeEntry this_ 
                               left outer join JobTask jobtask3_ 
                                            on this_.JobTaskId = jobtask3_.id 
                               left outer join Job jobtask_jo1_ 
                                            on jobtask3_.JobId = jobtask_jo1_.id 
                               left outer join JobFreeTagLink jobtask_jo2_ 
                                            on jobtask3_.JobId = 
                                               jobtask_jo2_.JobId 
                        where  jobtask3_.JobId = 3) 
       and this_.id in (select this_.id as y0_ 
                        from   JobTaskTimeEntry this_ 
                               left outer join JobTask jobtask3_ 
                                            on this_.JobTaskId = jobtask3_.id 
                               left outer join Job jobtask_jo1_ 
                                            on jobtask3_.JobId = jobtask_jo1_.id 
                               left outer join JobFreeTagLink jobtask_jo2_ 
                                            on jobtask3_.JobId = 
                                               jobtask_jo2_.JobId 
                        where  jobtask3_.JobId = 4) 
       and this_.id in (select this_.id as y0_ 
                        from   JobTaskTimeEntry this_ 
                               left outer join JobTask jobtask3_ 
                                            on this_.JobTaskId = jobtask3_.id 
                               left outer join Job jobtask_jo1_ 
                                            on jobtask3_.JobId = jobtask_jo1_.id 
                               left outer join JobFreeTagLink jobtask_jo2_ 
                                            on jobtask3_.JobId = 
                                               jobtask_jo2_.JobId 
                        where  jobtask3_.JobId = 5) 
       and this_.id in (select this_.id as y0_ 
                        from   JobTaskTimeEntry this_ 
                               left outer join JobTask jobtask3_ 
                                            on this_.JobTaskId = jobtask3_.id 
                               left outer join Job jobtask_jo1_ 
                                            on jobtask3_.JobId = jobtask_jo1_.id 
                               left outer join JobFreeTagLink jobtask_jo2_ 
                                            on jobtask3_.JobId = 
                                               jobtask_jo2_.JobId 
                        where  jobtask3_.JobId = 6) 
       and this_.id in (select this_.id as y0_ 
                        from   JobTaskTimeEntry this_ 
                               left outer join JobTask jobtask3_ 
                                            on this_.JobTaskId = jobtask3_.id 
                               left outer join Job jobtask_jo1_ 
                                            on jobtask3_.JobId = jobtask_jo1_.id 
                               left outer join JobFreeTagLink jobtask_jo2_ 
                                            on jobtask3_.JobId = 
                                               jobtask_jo2_.JobId 
                        where  jobtask3_.JobId = 7);



 Comments   
Comment by Ales Havlik (Inactive) [ 2014-09-10 ]

Is probably related to / duplicates MDEV-6407

Comment by Elena Stepanova [ 2014-09-15 ]

Assigning to psergei to see if it's really a duplicate of MDEV-6407 which is in progress now.

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