Hi Guys!
I have a MySQL query which takes quite a while to load. This is very frustrating for users when they do a search as it can take up to 10 seconds for the page to load. When I execute the function in phpMyAdmin it says the query took 1.34 seconds but in fact takes about 10 seconds for that page to load too.
I also run an EXPLAIN on the query and took a screenshot of the result. Any help to speed this query up would be greatly appreciated.
https://dl.dropbox.com/u/55641167/misc/Screen%20Shot%202012-08-16%20at%2008.24.20.png
SELECT jobs.job_title, jobs.job_url, jobs.job_salary, jobs.job_ote, jobs.job_perks, jobs.job_description, jobs.date_posted, companies.company_name, companies.company_url, companies.logo_thumbnail, count( applications.id ) AS applications, count( featured_jobs.id ) AS featured, l.display_name, l.postcode, l.display_county
FROM jobs
INNER JOIN companies ON companies.id = jobs.company_id
INNER JOIN jobs_industries ON jobs_industries.job_id = jobs.id
INNER JOIN jobs_job_types ON jobs_job_types.job_id = jobs.id
INNER JOIN (
SELECT job_id, if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name, postcode, display_county, location_id
FROM jobs_locations
GROUP BY job_id
) AS l ON l.job_id = jobs.id
LEFT OUTER JOIN applications ON applications.job_id = jobs.id
LEFT OUTER JOIN featured_jobs ON featured_jobs.job_id = jobs.id
WHERE jobs.status = 'on'
GROUP BY jobs.id
ORDER BY featured DESC , date_posted DESC