title: MySQL Index and Explain Plan
body: <p>I have this query:</p>
  <pre><code>SELECT * FROM dwDimDate d 
  LEFT JOIN tickets t FORCE INDEX FOR JOIN (idx_tickets_id_and_date) ON 
  DATE_FORMAT(t.ticket_date, '%Y%m%d') = d.date_key 
  LEFT JOIN sales s ON s.ticket_id = t.ticket_id
  WHERE d.date_key BETWEEN 20130101 AND 20131231
  GROUP BY d.date_key 
  </code></pre>
  <p>and I'm looking for help optimizing it. I've been reading everything I can on understanding the explain plan, and optimizing based off of that, but I can't seem to prevent MySQL from using an ALL type lookup on the tickets table.</p>
  <p><strong>INDEXES:</strong></p>
  <p><a href="https://i.stack.imgur.com/Crebn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Crebn.png" alt="enter image description here" /></a></p>
  <p><strong>EXPLAIN PLAN:</strong></p>
  <p><a href="https://i.stack.imgur.com/XiW4P.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XiW4P.png" alt="enter image description here" /></a></p>
  <p>I've tried using FORCE INDEX FOR JOIN to try and get it to index off of the date, but it doesn't seem to take the hint.</p>
  <p>The dwDimDate is a date dimension with days of the year, so in this scenario I would think it would be fast to limit to 365 days, and then find all the tickets within that date range. There should only be about 5K tickets within that date span.</p>
  <p>Any help would be greatly appreciated. I don't know how to figure out what strategy to employ to remove the &quot;ALL&quot; lookup. I'd like to understand how to do this in the future, so if you can help &quot;teach me to fish&quot;, that would be great.</p>
  <p><strong>EDIT</strong>
  The query currently takes 11 seconds to run, which is going to be a problem in production.</p>