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:

enter image description here

table index screenshots:

enter image description here

enter image description here

enter image description here

enter image description here

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
mysql group-by sql-order-by left-join
Edit
Report