[MDEV-6724] Slow query optimizer with certain subqueries - JIRA

Details

Type: Bug
Status: Open
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.5.33a, 10.0.14, 10.1.0
Fix Version/s: 10.0
Component/s: Optimizer
Labels:
Environment:
Centos 6.4 x64

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);

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

slow_query.sql
9 kB
2014-09-10 16:11

Issue Links

duplicates

MDEV-6407 Incredibly slow performance on SELECT query (and its corresponding EXPLAIN), works fine on MySQL

In Progress

relates to

MDEV-7275 Table elimination is not used for tables inside semi-join

Open

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Ales Havlik added a comment - 2014-09-10 16:24

Is probably related to / duplicates MDEV-6407

Show

Ales Havlik added a comment - 2014-09-10 16:24 Is probably related to / duplicates MDEV-6407

Hide

Permalink

Elena Stepanova added a comment - 2014-09-15 13:21

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

Show

Elena Stepanova added a comment - 2014-09-15 13:21 Assigning to Sergei Petrunia to see if it's really a duplicate of MDEV-6407 which is in progress now.

Slow query optimizer with certain subqueries

Details

Description

Gliffy Diagrams

Attachments

Attachments

Issue Links

Activity

People

Dates

Agile