Edit Question - KnowledgeHub

Title Body

I have a problem with a query which takes far too long (Over two seconds just for this simple query). On first look it appears to be an indexing issue, all joined fields are indexed, but i cannot find what else I may need to index to speed this up. As soon as i add the fields i need to the query, it gets even slower. SELECT `jobs`.`job_id` AS `job_id` FROM tabledef_Jobs AS jobs LEFT JOIN tabledef_JobCatLink AS jobcats ON jobs.job_id = jobcats.job_id LEFT JOIN tabledef_Applications AS apps ON jobs.job_id = apps.job_id LEFT JOIN tabledef_Companies AS company ON jobs.company_id = company.company_id GROUP BY `jobs`.`job_id` ORDER BY `jobs`.`date_posted` ASC LIMIT 0 , 50 Table row counts (~): tabledef_Jobs (108k), tabledef_JobCatLink (109k), tabledef_Companies (100), tabledef_Applications (50k) Here you can see the Describe. 'Using temporary' appears to be what is slowing down the query: table index screenshots: Any help would be greatly appreciated EDIT WITH ANSWER Final improved query with thanks to @Steve (marked answer). Ultimately, the final query was reduced from ~22s to ~0.3s: SELECT `jobs`.`job_id` AS `job_id` FROM ( SELECT * FROM tabledef_Jobs as jobs ORDER BY `jobs`.`date_posted` ASC LIMIT 0 , 50 ) AS jobs LEFT JOIN tabledef_JobCatLink AS jobcats ON jobs.job_id = jobcats.job_id LEFT JOIN tabledef_Applications AS apps ON jobs.job_id = apps.job_id LEFT JOIN tabledef_Companies AS company ON jobs.company_id

Tags (comma-separated) Cancel