I have added a new column with the date in seconds, created an index
over this column and modified the query a little bit. Now the query
takes less than 20 seconds. Since this is the worst case I'm happy with
the result.
Just for reference, the final query is:
SELECT STRFTIME('%Y-%m-%d %H %M', MIN(date)) AS Date,
(SELECT open FROM eurusd e2
WHERE e2.unix >= e1.unix / 300 * 300
ORDER BY e2.unix ASC LIMIT 1) AS Open,
MAX(high) as High,
MIN(low) as Low,
(SELECT close FROM eurusd e3
WHERE e3.unix < (e1.unix / 300 + 1) * 300
ORDER BY e3.unix DESC LIMIT 1) AS Close
FROM eurusd e1
GROUP BY e1.unix / 300
The query plan (that I cannot interpret) is:
0|0|0|SCAN TABLE eurusd AS e1 (~1000000 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE eurusd AS e2 USING INDEX unix_index (unix>?) (~250000
rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE eurusd AS e3 USING INDEX unix_index (unix<?) (~250000
rows)
Many thanks for your support.
Best regards
Rafael
El 13/12/2011 18:40, Simon Slavin escribió:
On 13 Dec 2011, at 5:38pm, jr wrote:
since much of that time will be spent in the strftime() calls and calculations,
how about adding a lookup table with pre-computed date/times?
Actually, with this much use of the date/time information I'd be storing it all
as numbers. And if you're wedded to the 5 minute period you might even want to
create a column specifically for which 5 minute period the time/date falls in.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
