MySQL: Optimizing query for calculation of financial BETA

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.

Here's my query for a 50 business day rolling window and I'd like you to help me optimize it for speed:

INSERT INTO betas (permno, index_id, DATE, beta) 
(SELECT 
  permno, index_id, s.date, IF(
    s.`seq` >= 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)

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.

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.

EDIT: Indexes: stock_series has PRIMARY KEY (permno,date) and UNIQUE KEY (permno,seq), index_series has PRIMARY KEY (index_id,date)

EXPLAIN EXTENDED result开发者_如何学运维s for ONE company (by including a WHERE s.permno=... restriction at the end):

EXPLAIN EXTENDED results for ALL ~500 companies:

---

Here i what the pros do: do NOT calcualte that in the databae. Pull data, calculate, reinsert. Where I work now they have a hugh grid doing that stuff in the end of day run. Yes, gris - like in a significant number of machines. We talk of producing gigabytes of csv files that then get reloaded into the database. Beta, Gamma, PnL on trades with 120.000 different elements. Databaes are NOT optimized for this.