CODEWITHSUNDEEP
mysqlsqlquery-performance
Dennis
Dennis

Reputation: 3690

Filtering query takes around 50 seconds to load

I have a page where users can search for vacancies with a very extensive set of filters. Two of those filters are checkboxes, where the user can select interests and accessibilities. When if selected, at least one of them has to be present on a certain vacancy to be displayed as a result.

I am using the CodeIgniter PHP Framework. An example of a generated query which makes my site crash looks like this:

SELECT v.*
     , o.name as orgname
     , GROUP_CONCAT(DISTINCT(vi.interest_id)) interests
     , GROUP_CONCAT(DISTINCT(i.description)) interestnames
     , GROUP_CONCAT(DISTINCT(i.name_brown)) interesticons
     , GROUP_CONCAT(DISTINCT(e.engagement_key)) engagement_key
  FROM vacancies v
  LEFT 
  JOIN organization o 
    ON v.org_id = o.org_id
  LEFT 
  JOIN vacancy_interests vi 
    ON v.vacancy_id = vi.vacancy_id
  LEFT 
  JOIN interests i 
    ON vi.interest_id = i.interest_id
  LEFT 
  JOIN engagement e 
    ON v.engagement = e.engagement_id
  LEFT 
  JOIN cities_be c 
    ON v.address_city_id = c.cities_be_id
  LEFT 
  JOIN vacancy_accessibility va  
    ON v.vacancy_id = va.vacancy_id
 WHERE v.is_deleted != 1
   AND v.status = 1
 GROUP 
    BY v.vacancy_id
HAVING MAX(vi.interest_id IN (3,4,6,7,9,10)) > 0 
   AND MAX(va.accessibility_id IN (2,1,3,4,5,6)) > 0 
 ORDER 
    BY v.modified_time DESC
 LIMIT 18

Every filter I use on my website goes smooth and gives me results, but the vacancy_accessibility table makes my search page crash completely when it is used. On localhost where i have approx 100 vacancies, it works good and fast. But my production server, where I have about 11k vacancies, all goes to hell and the search takes about 5-6 minutes to complete.

I have executed the query in MySQL workbench as pasted above, it takes about 52 seconds for the query to compute.

If I perform the same query, but only with the interests selected (this means I do not load the accessibility table via a LEFT JOIN, it works very fast (around 0.1sec). This does not really work the other way around, I've tried deleting the interest left join and the query is faster, but it is still about 16seconds. Note that I do need the interests join to be in there so this is not really an option, just for debugging.

The vacancy accessibility table is actually the exact same as the vacancy_interests. It contains a vacancy_id column and a reference column to the accessibility_id. The vacancy interests table contains 28k rows, while the vacancy accessibility table only contains 5.8k rows... I do notice that there are no FK's set in the accessibility table, while they are set in the interests table. I'm not sure whether this causes this massive delay (it's really not that many rows).

Does anyone see what is wrong with my Query that makes my page crash?

Upvotes: 1

Views: 83

Answers (1)

dnoeth
dnoeth

Reputation: 60472

There are several isssues with your current Select:

  • it has to do all the joins first and then aggregate before it starts to filter
  • those joins increase the number of rows, because some are they m-n instead of n-1, finally leading to the GROUP_CONCAT(DISTINCT)
  • all those joins are Outer Joins, never decreasing the number of rows

Try to apply the filter and the GROUP_CONCATs as soon as possible and switch to Inner Joins when possible. Applying those rule to the first part of your query:

SELECT `v`.*, 
   vi.interests, 
   vi.interestnames, 
   vi.interesticons

FROM `vacancies` AS `v`
JOIN 
 ( -- Derived Table to apply filter & concat as soon as possible
   -- also results in a single row per vacancy_id (n.1-join)
   SELECT `vacancy_id`
     ,GROUP_CONCAT(i.interest_id) AS interests
     ,group_concat(i.description) AS interestnames -- DISTINCT probably not needed
     ,group_concat(i.name_brown) AS interesticons -- DISTINCT probably not needed
   FROM `vacancy_interests` AS `vi`
   JOIN 
    ( SELECT interest_id
      FROM `interests`
      GROUP BY interest_id
      HAVING Max(interest_id) IN (3, 4, 6, 7, 9, 10)  
    ) AS `i`
   ON `vi`.`interest_id` = `i`.`interest_id`
 ) AS `vi`
ON `v`.`vacancy_id` = `vi`.`vacancy_id`
...

Now add the accessibility_id using a join to a similar Derived Table. When you applied all filter conditions, put the whole Select in another Derived Table including the LIMIT (reduding the number of rows) and finally join the remaining tables (again if possible using Inner Joins)

Upvotes: 3

Related Questions