Mysql – Just 4 Table Join query takes too much time and some time fail to execute

join;likeMySQLPHP

I am working on getting candidate detail from mysql database and for that i have created one query.

Is my query structure is wrong?

Here is the query:

SELECT  `candidate`.`id`, `candidate`.`email`, `candidate`.`fname`,
        `candidate`.`lname`, `candidate`.`id` as `can_id`, `candidate`.`update_date` AS `updated_at`,
        DATE_FORMAT(candidate.dob, '%d-%m-%Y') AS dob, `candidate`.`gender`,
        GROUP_CONCAT(DISTINCT candidate_experience.functional_area_name SEPARATOR ',') AS can_functional_area,
        GROUP_CONCAT(DISTINCT candidate_experience.industry_type_name SEPARATOR ',') AS can_industry_type_name,
        GROUP_CONCAT(DISTINCT qualification_name SEPARATOR ',') AS qualification_name,
        GROUP_CONCAT(DISTINCT candidate_key_skills.name SEPARATOR ',') AS skill,
        `candidate_desired_job`.`current_location`,
        `candidate_desired_job`.`total_experiance`, `candidate_desired_job`.`prefered_location`,
`candidate_desired_job`.`notice_period`,
IFNULL(candidate_desired_job.expected_salary, 'N') AS expected_salary
    FROM  `candidate`
    LEFT JOIN  `candidate_key_skills`  ON `candidate_key_skills`.`candidate_id` = `candidate`.`id`
    LEFT JOIN  `candidate_qualification`  ON `candidate_qualification`.`candidate_id` = `candidate`.`id`
    LEFT JOIN  `candidate_desired_job`  ON `candidate_desired_job`.`candidate_id` = `candidate`.`id`
    LEFT JOIN  `candidate_experience`  ON `candidate_experience`.`candidate_id` = `candidate`.`id`
    WHERE  ( `name` LIKE '%PHP%' ESCAPE '!' )
      AND  ( `prefered_location` LIKE '%Ahmedabad%' ESCAPE '!' )
      AND  `total_experiance` >= '-1'
      AND  `total_experiance` <= '40'
      AND  `expected_salary` >= '0'
      AND  `expected_salary` <= '500000'
      AND  ( `candidate_experience`.`industry_type_name` LIKE '%Accommodation and  Food Services%' ESCAPE '!' 
           )
      OR  `carrier_summary` LIKE '%PHP%' ESCAPE '!'
      OR  `carrier_summary` LIKE '%Ahmedabad%' ESCAPE '!'
      OR  `carrier_summary` LIKE '%Accommodation
      and  Food Services%' ESCAPE '!'
      AND  `candidate`.`id` >0
    GROUP BY  `candidate_key_skills`.`candidate_id`, `candidate_desired_job`.`id`
    ORDER BY  `candidate_desired_job`.`id` DESC

Best Answer

Red flag! x AND y OR z is the same as (x AND y) OR z. I suspect you need parentheses around the ORs.

And something seems to be missing (even before my edit) on the last OR.

(I suggest you use short "aliases" to help de-clutter the query.)

Please qualify all columns. Meanwhile, I will assume all the columns mentioned in the WHERE come from candidate, else some of the following will not be correct....

Leading wildcards with LIKE prevents usage of indexes, so I will ignore name, etc. Can you use FULLTEXT instead of LIKE?

ids are usually positive values, so why do you have AND candidate.id >0??

So, we are down to two choices for a useful INDEX: (total_experiance) or (expected_salary). But I suspect neither of those will be very useful.

As for the 4 LEFT JOINs -- Are they "many:1"

Ouch. 2 different tables in the GROUP BY. That forces the generation of a temp table leading into the GROUP BY. That temp table will have lots of rows, and be slow.

Since the ORDER BY is different than the GROUP BY, there will be another temp table and sort. This can be avoided by saying 

ORDER BY `candidate_key_skills`.`candidate_id` DESC,
         `candidate_desired_job`.`id` DESC

to match the GROUP BY. This will speed up the query a little.

Ordinarily I would suggest a subquery instead of a LEFT JOIN, thereby avoiding some of the "inflation" caused by the join. But I see that each case involves two select items.

Sorry, there is not much that will speed up the query.

Related Question