MYSQL Simple Moving Average Calculation

Question

The following MySql update state seems to take too long to complete a set of records (~ 5000 records). It takes an average of 12 seconds to complete the update instruction. I am currently planning to launch this calculation on 5 different periods and about 500 different characters. This translates to 12 seconds * 5 calculations * 500 characters = 30,000 seconds or 8..33 hours.

Update operation:

UPDATE tblStockDataMovingAverages_AAPL JOIN (SELECT t1.Sequence, ( SELECT AVG(t2.Close) FROM tblStockDataMovingAverages_AAPL AS t2 WHERE (t1.Sequence - t2.Sequence)BETWEEN 0 AND 7 )AS "8SMA" FROM tblStockDataMovingAverages_AAPL AS t1 ORDER BY t1.Sequence) AS ma_query ON tblStockDataMovingAverages_AAPL.Sequence = ma_query.Sequence SET tblStockDataMovingAverages_AAPL.8MA_Price = ma_query.8SMA 

Table design:

 CREATE TABLE `tblStockDataMovingAverages_AAPL` ( `Symbol` char(6) NOT NULL DEFAULT '', `TradeDate` date NOT NULL DEFAULT '0000-00-00', `Sequence` int(11) DEFAULT NULL, `Close` decimal(18,5) DEFAULT NULL, `200MA_Price` decimal(18,5) DEFAULT NULL, `100MA_Price` decimal(18,5) DEFAULT NULL, `50MA_Price` decimal(18,5) DEFAULT NULL, `20MA_Price` decimal(18,5) DEFAULT NULL, `8MA_Price` decimal(18,5) DEFAULT NULL, `50_200_Cross` int(5) DEFAULT NULL, PRIMARY KEY (`Symbol`,`Sequence`), KEY `idxSequnce` (`Sequence`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ 

Any help on speeding up the process is welcome.

Result of selection Explanation:

 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL idxSymbol_Sequnce 11 NULL 5205 Using index; Using filesort 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5271 Using where 

Answer 1

This should be a little better:

 update tblStockDataMovingAverages_AAPL join ( select t1.sequence as sequence, avg(t2.close) as av from tblStockDataMovingAverages_AAPL t1 join tblStockDataMovingAverages_AAPL t2 on t2.sequence BETWEEN t1.sequence-7 AND t1.sequence group by t1.sequence ) t1 on tblStockDataMovingAverages_AAPL.sequence = t1.sequence set 8MA_Price = t1.av 

As for my BETWEEN statement: field1 OPERATOR expression(field2) easier to optimize than expression(field1, field2) OPERATOR expression to ON . I think this is true for BETWEEN .

It looks like ORDER BY is not required in your query, and deleting it can speed up your query to a ton.

If any of the stock symbols appears in the same table, paste all this into one update request (different periods will not work), this will most likely be faster than starting for each of them.

As already mentioned, adding an index to Close can help.

Answer 2

you can optimize it a bit by adding an index to the Close field. AVG function should be more efficient. Please share a dump of your dataset to see it closer.