0

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.

2 Answers 2

1
WHERE  BaseCurrency = 'EUR'
  AND  QuoteCurrency = 'USD'
ORDER BY  RateTime DESC

Needs

INDEX(BaseCurrency, QuoteCurrency, RateTime)

and

WHERE  CurrencyFrom = 'EUR'
  AND  CurrencyTo = 'USD'
  AND  ExchangeTime <  '2018-01-29 12:00:00'
ORDER BY  ExchangeTime DESC

needs

INDEX(CurrencyFrom, CurrencyTo, ExchangeTime)

Ditto for the third.

More discussion of how I came up with those indexes is in my Cookbook .

The reason for the sudden slowdown is buried in the size of the table, the value of innodb_buffer_pool_size, the number of rows that your too-short index touched, etc.

My indexes will be fast, regardless of any of those issues (for those queries).

3
  • The CurrencyFrom, and BaseCurrency columns are the same, that is just a typo in my question:( Regardless, the answer was correct, the order of the columns in the index did cause the issue, and by re-indexing the table, I've managed to solve the problem, so I will accept this answer as the correct one. Mar 4, 2018 at 18:22
  • @Rick: What if we want to get latest change amount before particular time and add group by? I am having same issue but in my case I need to do group by and then take latest change of each group. Can you suggest on that? Apr 5, 2019 at 18:46
  • @RushabhPatel - "latest" -- That sounds like a "groupwise-max" problem. Follow that tag.
    – Rick James
    Apr 9, 2019 at 18:15
0

I'll assume that the last 2 queries have the same execution plan (something you can check with EXPLAIN). But I suggest you check if the execution plan is optimal, which means, if an index is used for both the WHERE and the ORDER BY clauses.

The answer to your question is simple: cache. Data that are read from time to time are probably stored in InnoDB buffer pool, others aren't. Check if you can increase innodb_buffer_pool_size - the general recommendation is, 80% of total memory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.