Results 1 to 5 of 5
Thread: Very slow MySQL query
Aug 15, 2012, 23:32 #1
Very slow MySQL query
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.
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