title: Best method to fill in gaps in a time series in a Mysql query
body: <p>I need to fill in the gaps of a time series of a mysql query result set. I'm in the process of testing the option of doing an outer join with a helper table that contains all of the data points of the time series (as indicated in this thread: <a href="https://stackoverflow.com/questions/1877874/how-to-fill-date-gaps-in-mysql">How to fill date gaps in MySQL?</a>).</p>
  
  <p>The issue I'm running into is that adding this join significantly increases the query response time (it goes from sub 1 sec to 90 seconds). </p>
  
  <p>Here's the original query:</p>
  
  <pre><code>select date_format(fact_data7.date_collected,'%Y-%m') as date_col
     , date_format(fact_data7.date_collected,'%d-%H:%i:%s') as time_col
     , fact_data7.batch_id,fact_data7.value as fdvalue,entities.ticker as ticker
     , date_format(fact_data7.date_collected,'%Y-%m-%d') as date_col2
     , date_format(fact_data7.date_collected,'%Y') as year 
  from fact_data7  
  JOIN entities on fact_data7.entity_id=entities.id  
  where (1=1)
    AND ((entities.id= 963
        AND fact_data7.metric_id=1
        ))
    AND date_format(fact_data7.date_collected,'%Y-%m') &gt; '2008-01-01'
  order by date_col asc
  </code></pre>
  
  <p>and here is the query with the outer join to the helper table (month_fill) added:</p>
  
  <pre><code>select date_format(month_fill.date,'%Y-%m') as date_col
      , date_format(fact_data7.date_collected,'%d-%H:%i:%s') as time_col
      , fact_data7.batch_id,fact_data7.value as fdvalue
      , entities.ticker as ticker
      , date_format(fact_data7.date_collected,'%Y-%m-%d') as date_col2
      , date_format(fact_data7.date_collected,'%Y') as year 
  from fact_data7
  JOIN entities
    on fact_data7.entity_id=entities.id  
  RIGHT OUTER JOIN month_fill
     on date_format(fact_data7.date_collected,'%Y-%m') =  date_format(month_fill.date,'%Y-%m')  
  where (1=1)
    AND (
        (entities.id= 963 AND fact_data7.metric_id=1)
        OR (entities.id is null and fact_data7.metric_id is null)
        )
    AND date_format(month_fill.date,'%Y-%m') &gt; '2008-01-01'
  order by date_col asc
  </code></pre>
  
  <p>Can I restructure the query to improve the performance is there an alternate solution to achieve what I'm looking for?</p>
  
  <p>Update 11/15:</p>
  
  <p>Here's the EXPLAIN output for the 1st query:</p>
  
  <pre><code>id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
  1   SIMPLE  entities    const   PRIMARY     PRIMARY     4   const   1   Using filesort
  1   SIMPLE  fact_data7  ALL     NULL    NULL    NULL    NULL    230636  Using where
  </code></pre>
  
  <p>Here's the EXPLAIN output for the 2nd query:</p>
  
  <pre><code>id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
  1   SIMPLE  month_fill  index   NULL    date    8   NULL    204     Using where; Using index; Using temporary; Using filesort
  1   SIMPLE  fact_data7  ALL     NULL    NULL    NULL    NULL    230636  Using where
  1   SIMPLE  entities    eq_ref  PRIMARY     PRIMARY     4   findata.fact_data7.entity_id    1   Using where
  </code></pre>