CODEWITHSUNDEEP
mysqlperformancemoving-average
user1869801
user1869801

Reputation: 43

MYSQL Simple Moving Average Calculation

The following MySql update state seems to take an excessive amount of time to execute for the recordset provided (~5000 records). The update statement below takes on average 12 seconds to execute. I currently plan to run this calculation for 5 different periods and about 500 different stock symbols. This translates into 12secs * 5 calculations * 500 symbols = 30,000 seconds or 8..33 hrs.

Update Statement:

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 sppeding up the process would be greatly appreciated.

Output of Select Explain:

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

Upvotes: 4

Views: 1798

Answers (2)

Bernhard Barker
Bernhard Barker

Reputation: 55609

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

With regard to my BETWEEN statement: field1 OPERATOR expression(field2) is easier to optimise than expression(field1, field2) OPERATOR expression in the ON condition. I think this holds for BETWEEN.

It looks like the ORDER BY in your query is unnecessary and removing it might speed your query up a ton.

If any of the stock symbols appear in the same table, stick all these into a single update query (different periods won't work though), this would likely be way faster than running it for each.

As already suggested, adding an index to Close may help.

Upvotes: 1

Константин Рекунов
Константин Рекунов

Reputation: 457

you can optimize it slightly by adding index to Close field. AVG function have to be more effective. Please share dump of your dataset to see it more close.

Upvotes: 1

Related Questions