Re: Moving average query taking too long to run

[sowa furan]

Hello,

I'm not the thread creator but I used your solution for the same purpose and I would like to thank you.
I have a question though, using the same query structure on a table with ~50,000 rows, the query takes around 2 seconds.
Here's my query, which is the same as your solution but with my own columns name :

SELECT a.datasourcepriceobservationid,a.sampletime, a.securitymetadataid,
ROUND(avg(b.closeprice),2) AS sma5
FROM datasourcepriceobservation AS a
JOIN datasourcepriceobservation AS b USING(securitymetadataid)
WHERE b.datasourcepriceobservationid BETWEEN a.datasourcepriceobservationid-4 AND a.datasourcepriceobservationid
AND a.datasourcepriceobservationid >= 5
GROUP BY a.sampletime, a.datasourcepriceobservationid
ORDER BY a.securitymetadataid, a.datasourcepriceobservationid;


But if I tried to create an index as you suggest at the end of your post, the query takes 125 seconds.
If I create only 1 covering index or both covering indexes in the same order as your post, it takes around 125 seconds. If I create only 1 index on 1 column (securitymetadataid), it takes 25 seconds.

My problem is I would like to use this query on a table with around 2 millions rows, and this takes more than 30 minutes.
Why having the index takes more time than without ?

Originally, on datapriceobservation table, there is no index except the primary key 'datasourcepriceobservationid' where each row is unique.

Thamk you very much for your assistance.