title: Slow SQL query maxing out CPU
body: <p>I've inherited a site to look after and have a problem with an sql query which is maxing out CPU when the job runs on the site.</p>
  
  <p>Unfortunately, I'm a front end developer and although I'm okay with simple sql this is too complex for me. The problem appears to be in the second EXISTS and its connection with the main query.</p>
  
  <p>Here's the code, it should gather a list of item IDs to send to a company.</p>
  
  <p>Is there anything obviously wrong with this query?</p>
  
  <pre><code>SELECT mc.id AS company_id,
         mi.id AS item_id,
         EXISTS ( 
          SELECT 1 
           FROM mysite AS m2
           JOIN mysite_order AS mo2 
             ON m2.mysite_order_id = mo2.id 
          WHERE mo2.mysite_item_id = mo.mysite_item_id 
                AND m2.company_email_sent = 'Yes'
         ) AS email_sent_before, 
         m.id AS mysite_id,
         m.mysite_code_prefix,
         m.mysite_code_suffix
    FROM mysite_company AS mc
    JOIN mysite_item AS mi
      ON mc.id = mi.company_id 
    JOIN mysite_order AS mo 
      ON mi.id = mo.mysite_item_id
    JOIN mysite AS m
      ON mo.id = m.mysite_order_id
   WHERE mi.avail_end_date &lt;= CURDATE()
     AND EXISTS ( 
             SELECT 1
               FROM mysite AS m3
               JOIN mysite_order AS mo3
                 ON m3.mysite_order_id = mo3.id
              WHERE mo3.mysite_item_id = mo.mysite_item_id 
                AND m3.company_email_sent = 'No' )
  ORDER BY company_id ASC, item_id ASC, m.mysite_code_prefix ASC,
              m.mysite_code_suffix ASC
  LIMIT 1
  </code></pre>