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