MySQL - SQLite How to improve this very simple query?

Question

I have one simple but large table.

id_tick   INTEGER      eg: 1622911
price     DOUBLE       eg: 1.31723
timestamp DATETIME     eg: '2010-04-28 09:34:23'

For 1 month of data, I have 2.3 million rows (150 MB)

My request is aimed at returning the last price at a given time.

First I created a SQLite table and used the query:

SELECT max(id_tick), price, timestamp 
FROM EURUSD 
WHERE timestamp <='2010-04-16 15:22:05'

It works in 1.6s.

Since I need to run this query several thousand times, 1.6s is too long ...

Then I set up the MySQL table and changed the query (the max function is different from MySQL to SQLite):

SELECT id_tick, price, timestamp
FROM EURUSD
WHERE id_tick = (SELECT MAX(id_tick) 
                 FROM EURUSD WHERE timestamp <='2010-04-16 15:22:05')

Runtime gets a lot worse than 3.6s (I know I can avoid an auxiliary query using ORDER BY and LIMIT 1, but this does not improve the runtime.)

Right now I only use one month of data, but at some point I will have to use a few years.

My questions are as follows:

• Is there any way to improve my query?
• , ?
• ?

!

Answer 1

1) ,

2) , id_tick PRIMARY KEY, Clustered Index, , id_tick ( MAX)

:

SELECT id_tick, price, timestamp 
FROM EURUSD 
WHERE id_tick = (SELECT id_tick
                   FROM EURUSD WHERE timestamp <='2010-04-16 15:22:05'
                   ORDER BY id_tick DESC
                   LIMIT 1)

janmoesen, id_tick timestamp

Answer 2

?

timestamp / id_tick .

?

WHERE timestamp >= '2010-04-15 15:22:05' AND timestamp <= '2010-04-16 15:22:05'

MAX.

Answer 3

Do you analyze using ALL ticks for large intervals? I tried to filter the data in graphs of minutes / hour / day, etc.

Answer 4

OK, I think my index was somehow damaged, re-indexing greatly improved performance.

Now runs at 0.0012s (not cached)

SELECT id_tick, price, timestamp
FROM EURUSD
WHERE timestamp <= '2010-05-11 05:30:10'
ORDER by id_tick desc
LIMIT 1

Thank!