title: MySQL: Grouped/Ordered/Left Join query very slow
body: <p>I have a problem with a query which takes far too long (Over two seconds just for this simple query).</p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Table row counts (~): tabledef_Jobs (108k), tabledef_JobCatLink (109k), tabledef_Companies (100), tabledef_Applications (50k)</p>
  
  <p>Here you can see the Describe. 'Using temporary' appears to be what is slowing down the query: </p>
  
  <p><img src="https://i.stack.imgur.com/dR1AW.png" alt="enter image description here"></p>
  
  <p>table index screenshots: </p>
  
  <p><img src="https://i.stack.imgur.com/0OwN7.png" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/Labid.png" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/0f3oY.png" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/mE4Qe.png" alt="enter image description here"></p>
  
  <p>Any help would be greatly appreciated</p>
  
  <p><strong>EDIT WITH ANSWER</strong></p>
  
  <p>Final improved query with thanks to @Steve (marked answer). Ultimately, the final query was reduced from ~22s to ~0.3s:</p>
  
  <pre><code>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 = company.company_id
  GROUP BY  `jobs`.`job_id` 
  ORDER BY  `jobs`.`date_posted` ASC
  LIMIT 0 , 50
  </code></pre>