MySQL relationship based query runs below 1s but fails when I add a little more conditions/complexity (No error, InnoDB)

Question

I'm running a laravel/lumen app. The SQL is auto generated using Eloquent so it might be a reason it's wonky. It's querying qualifications table based on exists on groups and dictionaries tables which are in many-many relationship to it.

Following query fails - It hangs running infinitely... Clogs mysql. (Gives no error - empty mysql, php, apache logs).

https://codebeautify.org/sqlformatter/y22875bc2

However if i substract 1-2 random conditions, hence reducing complexity it runs perfectly beneath 1s.

To give some context I'm running a following api query.

http://localhost:8000/qualification
?with=dictionaries,groups1,groups2
&limit=20
&offset=0
&metadata=false
&status=funkcjonująca,|włączona
&category=!uregulowane
&groups1>name_pl=NAUKI ŚCISŁE I PRZYRODNICZE
&groups2>name_pl=Geografia,geologia,geofizyka
&hobby=przyroda
&expectation=kultura osobista,zainteresowanie modą,dobry węch
&edulvl=Branżowe,|Średnie,|Wyższe,|Inne pozaformalne

The DB runs on InnoDB. It has proper keys and indices.

I`d say it behaves like I'm running into some resources, memory cap? Am I wrong?

When I run an explain select on this I get the same behavior. Runs when a little simplier. Fails when full query is tested.

Answer 1

Without having more detail it is impossible to tell where things are going wrong. There are some things which can be cleaned up. The following two groups of or exists() criteria can be condensed -

select * 
from `qualifications` 
where (
    exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    or exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    )
)

can be reduced to -

select * 
from `qualifications` 
where (
    exists (
        select * 
        from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
        where `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and (
            (`type` = ? and `content_pl` = ?) or
            (`type` = ? and `content_pl` = ?)
        )
        /* or if type is same for both
        and `type` = ?
        and `content_pl` IN (?, ?)
        */
    ) 
)

Similarly -

select * 
from `qualifications` 
where (
    exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    or exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    or exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    or exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    )
)

can be reduced to -

select * 
from `qualifications` 
where (
    exists (
        select * 
        from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
        where `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and (
            (`type` = ? and `content_pl` = ?) or
            (`type` = ? and `content_pl` = ?) or
            (`type` = ? and `content_pl` = ?) or
            (`type` = ? and `content_pl` = ?)
        )
        /* or if type is same for all four
        and `type` = ?
        and `content_pl` IN (?, ?, ?, ?)
        */
    ) 
)

You can use a similar approach where there is a group of and exists() criteria -

select * 
from `qualifications` 
where (
    exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    and exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    ) 
    and exists (
      select * 
      from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
      where 
        `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ? 
        and `content_pl` = ?
    )
)

can be reduced to -

select * 
from `qualifications` 
where (
    exists (
        select
            `dictionary_qualification`.`qualification_id`,
            count(distinct `content_pl`) num_criteria_matched
        from `dictionaries` 
        inner join `dictionary_qualification` on `dictionaries`.`id` = `dictionary_qualification`.`dictionary_id` 
        where `qualifications`.`id` = `dictionary_qualification`.`qualification_id` 
        and `type` = ?
        and `content_pl` IN (?, ?, ?) # note three values and we want all three to be present
        group by `dictionary_qualification`.`qualification_id`
        having num_criteria_matched = 3 # again, this is the number of values for which we require a match
    ) 
)