Hi Guys,
Im trying to make a MySQL query as fast as it can possibly be and I want some advice. Below is the current query. Also, please see the link below for the results from the EXPLAIN command on that particular query. It currently takes around 4-5 seconds to load 25,000 results. Im looking to try and get it under 1 second. Any initial thoughts?
SELECT jobs.job_title, companies.company_name, companies.company_url, companies.logo_thumbnail, jobs.job_url, jobs.job_salary, jobs.job_ote, jobs.job_perks, jobs.job_description, jobs.location, jobs.date_posted, jobs.cv_library_ref, industries.industry, job_types.job_type, count( applications.id ) AS applications, count( featured_jobs.id ) AS featured
FROM jobs
INNER JOIN companies ON companies.id = jobs.company_id
INNER JOIN industries ON industries.id = jobs.industry
INNER JOIN job_types ON job_types.id = jobs.job_type
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
EXPLAIN RESULTS: View image: Screen shot 2011 08 25 at 15 18 05
Thanks in advance.