I have a MySQL table, which stores exchange rates for two currencies, with a specific date. This table is updated every 15 second by an external script, and the data it contains is rather large, and will get even larger in time (since 2017 september, we have gathered ~8 million rows, but the updates at the beginning where with a 2 minute time scale).
The tables structure looks as follows:
|---------------------------------------------------------|
| RateId | RateTime | BaseCurrency | QuoteCurrency | Rate |
|---------------------------------------------------------|
There are the following indexes in place on this table:
PRIMARY BTREE Yes No RateId 7534199 A No
BaseCurrency BTREE No No BaseCurrency 5199 A No
QuoteCurrency 17938 A No
QuoteCurrency BTREE No No QuoteCurrency 14 A No
RateTime BTREE No No RateTime 753419 A No
When I query current data, for example:
SELECT *
FROM ExchangeRates
WHERE BaseCurrency = 'EUR'
AND QuoteCurrency = 'USD'
ORDER BY RateTime DESC
LIMIT 0,1
the query is very fast, around 0.0029 seconds.
When I try to query historical data, like:
SELECT *
FROM `ExchangeTable`
WHERE CurrencyFrom = 'EUR'
AND CurrencyTo = 'USD'
AND ExchangeTime < '2018-01-29 12:00:00'
ORDER BY ExchangeTime DESC
LIMIT 0,1
it is still fast, around 0.0008 seconds.
The problem starts when I try to query data, that we are not refreshing anymore, but I would still need historical information about. For example, we stopped gathering the rate for EUR->RON 6 months ago. If I would like to do the following query:
SELECT *
FROM `ExchangeTable`
WHERE CurrencyFrom = 'EUR'
AND CurrencyTo = 'RON'
AND ExchangeTime < '2018-01-29 12:00:00'
ORDER BY ExchangeTime DESC
LIMIT 0,1
it takes around ~9.5897 seconds to execute, with the row found dated in 2017-08-25 13:07:00.
Why is this query so slow, while the rest of the queries are fast? What could I do to improve these kind of queries?
We are using MariaDB, with InnoDB storage engine.
