title: Methods of speeding up a MySQL Query which has 12 subqueries
body: <p>The following query returns the correct data but I'd like to see if there's a better way of doing this.  The query should return the number of cases for each month within a 12 month period where a record exists within the past 2 months.  The idea is to get number of accounts that ordered during the month in question and at least one of the previous 2 months. Also, please note that every value in the table for data_date will always be the 1st of the month.</p>
  
  <pre><code>SELECT 
  sum( 
          case 
              WHEN    a.data_date = '2013-03-01'
                      and exists( 
                              select 1 from sales mth1 
                              where mth1.client_id = a.client_id
                              and 
                                      data_date BETWEEN '2013-01-01' 
                                      and 
                                      '2013-02-01' 
  
                      ) 
              then case_qty 
              ELSE 0 END
  ) AS M1 , 
  sum( 
          case 
                  WHEN a.data_date = '2013-04-01' 
                          and exists( 
                                  select 1 from sales mth2 
                                  where mth2.client_id = a.client_id 
                                  and mth2.data_date BETWEEN '2013-02-01' 
                                          and 
                                          '2013-03-01' 
                          ) 
                  then case_qty 
                  ELSE 0 END
      ) AS M2 , 
      sum( case WHEN a.data_date = '2013-05-01' and exists( select 1 from sales mth3 where mth3.client_id = a.client_id and mth3.data_date BETWEEN '2013-03-01' and '2013-04-01' )  then case_qty ELSE 0 END) AS M3 , 
      sum( case WHEN a.data_date = '2013-06-01' and exists( select 1 from sales mth4 where mth4.client_id = a.client_id and mth4.data_date BETWEEN '2013-04-01' and '2013-05-01' )  then case_qty ELSE 0 END) AS M4 , 
      sum( case WHEN a.data_date = '2013-07-01' and exists( select 1 from sales mth5 where mth5.client_id = a.client_id and mth5.data_date BETWEEN '2013-05-01' and '2013-06-01' )  then case_qty ELSE 0 END) AS M5 , 
      sum( case WHEN a.data_date = '2013-08-01' and exists( select 1 from sales mth6 where mth6.client_id = a.client_id and mth6.data_date BETWEEN '2013-06-01' and '2013-07-01' )  then case_qty ELSE 0 END) AS M6 , 
      sum( case WHEN a.data_date = '2013-09-01' and exists( select 1 from sales mth7 where mth7.client_id = a.client_id and mth7.data_date BETWEEN '2013-07-01' and '2013-08-01' )  then case_qty ELSE 0 END) AS M7 , 
      sum( case WHEN a.data_date = '2013-10-01' and exists( select 1 from sales mth8 where mth8.client_id = a.client_id and mth8.data_date BETWEEN '2013-08-01' and '2013-09-01' )  then case_qty ELSE 0 END) AS M8 , 
      sum( case WHEN a.data_date = '2013-11-01' and exists( select 1 from sales mth9 where mth9.client_id = a.client_id and mth9.data_date BETWEEN '2013-09-01' and '2013-10-01' )  then case_qty ELSE 0 END) AS M9 , 
      sum( case WHEN a.data_date = '2013-12-01' and exists( select 1 from sales mth10 where mth10.client_id = a.client_id and mth10.data_date BETWEEN '2013-10-01' and '2013-12-01' )  then case_qty ELSE 0 END) AS M10 , 
      sum( case WHEN a.data_date = '2014-01-01' and exists( select 1 from sales mth11 where mth11.client_id = a.client_id and mth11.data_date BETWEEN '2013-11-01' and '2013-12-01' )  then case_qty ELSE 0 END) AS M11  
  FROM sales as a 
  INNER JOIN Products AS P ON P.product_id = a.product_id  
  WHERE a.client_id IN ('123') 
  AND a.data_date BETWEEN '2013-03-01' AND '2013-12-01' AND a.case_qty &gt; 0;
  </code></pre>
  
  <p><a href="http://i.stack.imgur.com/ckTLw.png" rel="nofollow">Here's a screen shot of the explain</a></p>
  
  <p><a href="http://i.stack.imgur.com/qqCDC.png" rel="nofollow">Here's a screen shot of the indexes</a></p>