title: MySQL: Optimizing query for calculation of financial BETA
body: <p>in finance, a stock's beta is the covariance between the stock's daily returns and an index' daily returns divided by the variance of the index daily returns. I try to calaculate beta for set of stocks and a set of indices.</p>
  
  <p>Here's my query for a 50 business day rolling window and I'd like you to help me optimize it for speed:</p>
  
  <pre><code>INSERT INTO betas (permno, index_id, DATE, beta) 
  (SELECT 
    permno, index_id, s.date, IF(
      s.`seq` &gt;= 50, 
      (SELECT 
   (AVG(s2.log_return*i2.log_return)-AVG(s2.log_return)*AVG(i2.log_return))/VAR_POP(i2.log_return) AS beta
      FROM
        stock_series s2
        INNER JOIN `index_series` i2 ON i2.date=s2.date 
        WHERE i2.index_id=i.index_id AND s2.permno = s.permno 
        AND s2.`seq` BETWEEN s.`seq` - 49 AND s.`seq` 
        GROUP BY index_id,permno), NULL)
     AS beta 
  FROM
    stock_series s
    INNER JOIN `index_series` i ON i.index_id IN ('SP500') AND i.date=s.date
   ) 
  ON DUPLICATE KEY 
  UPDATE beta=  VALUES (beta)
  </code></pre>
  
  <p>Both main tables are already ordered by entity and date in ascending order, and they already include log daily returns as well as a "seq" column. Seq sequentally enumerates all daily rows company- (or index-) wise, i.e. seq starts over at 1 for every new stock or index in the table and counts up to the number of total number of rows for a given entity. I created it to allow for the rolling window.</p>
  
  <p>As of now, with 500 firms and 1 index, the query takes like forever to complete.
  Let me know any optimization that comes to your mind, like views, stored procs, temp tables, and if you find any inconsistencies, of course.</p>
  
  <p>EDIT: Indexes:
  stock_series has PRIMARY KEY (permno,date) and UNIQUE KEY (permno,seq),
  index_series has PRIMARY KEY (index_id,date)</p>
  
  <p>EXPLAIN EXTENDED results for ONE company (by including a WHERE s.permno=... restriction at the end):
  <img src="https://i.stack.imgur.com/dJSrx.gif" alt="EXPLAIN EXTENDED of above SELECT query"></p>
  
  <p>EXPLAIN EXTENDED results for ALL ~500 companies:
  <img src="https://i.stack.imgur.com/OG8qP.gif" alt="enter image description here"></p>