title: Mysql Large Table Join Query very slow not Key indexes issue
body: <pre><code>SELECT  t1.*
      FROM  
        ( SELECT  key_a,key_b,MAX(date) as date
              FROM  large_table
              WHERE  date &lt;= **20150126**
              group by  key_a,key_b 
        ) AS t2
      JOIN  large_table AS t1 USING(key_a,key_b ,date) 
  </code></pre>
  
  <blockquote>
    <p>large_table = 1,223,001,206 rows of data</p>
  </blockquote>
  
  <pre><code>Primary Key key_a,key_b,date
  key on key_b
  key on date
  </code></pre>
  
  <p>There are numerous empty dates between rows for a &amp; b that I want the most recent behind or on the <strong>"Date"</strong> entered.</p>
  
  <p>Is it the Mysql Join settings causing it to be slow ?</p>
  
  <p>I can copy the entire set of <strong>a &amp; b</strong> data with an INSERT to a temp table just by selecting all the rows and then run the same query on the temp table, but why do multi queries (insert selected, then select from) when only 1 is needed.</p>
  
  <p>The query above only has 4,128,548 total results in the temp insert all dates table, and the date specific returns under 180,000 total.</p>
  
  <p>Not table optimization, not keys, is it Max sort length, Join Buffer size , I have 128 gig ram, on a 32 core server running this, there is no reason for it to be slow, just never bulk insert this large of a single table to run Join queries on prior if anyone else has dealt with tables this size any info greatly appreciated.</p>
  
  <p>Edited query, yes it's late long day had Distinct when it wasn't needed or in actual query</p>