Search is painfully slow

Databases
21

Looking at it more I think removing the file sorts is the main problem. There is no need to group or concat the data until the outermost query. I would be skeptical and have to test though whether you would be getting a significant performance boost using subqueries w/ inner joins or just join all the tables with left outer. Particularly when it comes to MySQL.


SELECT 
       u.*
      ,GROUP_CONCAT(DISTINCT ujt.job_type SEPARATOR ' ')
      ,GROUP_CONCAT(DISTINCT ui.industry SEPARATOR ' ')
  FROM
      users u
  LEFT OUTER
  JOIN 
     (SELECT
            ujt.user_id
           ,jt.job_type
        FROM
           users_job_types ujt
       INNER
        JOIN
           job_types jt
          ON
           ujt.job_type_id = jt.id) ujt
    ON
      u.id = ujt.user_id
  LEFT OUTER
  JOIN
      (SELECT
            ui.user_id
            ,i.industry
         FROM
            users_industries ui
        INNER
         JOIN
            industries i
           ON
            ui.industry_id = i.id) ui
    ON
      u.id = ui.user_id
 WHERE
      u.status = 1
   AND
      u.cv_hide = 0
 GROUP
    BY
      u.id
 ORDER
    BY
      u.cv_date DESC
22

that will produce an inefficient proliferation of cross join intermediate rows, which you conveniently hide by using DISTINCT in the GROUP_CONCAT

i don’t see that working any better