CODEWITHSUNDEEP
mysqlsql
Alex108dev
Alex108dev

Reputation: 1

How optimize SQL query?

I have a query:

SELECT * FROM `trades`
WHERE `symbol` = 'ICX/BTC' AND `timestamp` >= :since AND `timestamp` <= :until
ORDER BY `timestamp`
LIMIT 50000

It's take long time to execute (more 5 min). I have index by symbol and timestamp columns

How I can optimize this query?

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

For this query:

SELECT t.*
FROM trades AS t
WHERE t.symbol = 'ICX/BTC' AND t.timestamp >= :since AND t.timestamp <= :until
ORDER BY t.timestamp
LIMIT 50000;

(which I just rewrite a bit so I can follow it more easily)

You want an index on trades(symbol, timestamp).

However, you appear to be selecting a very large number of rows, so this might still take a long time. The index should be used both for the WHERE clause and the ORDER BY.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108651

In your query, you are retrieving data from just one table and your filter criteria are ...

  1. equality on symbol

  2. range scan low-to-high on timestamp.

Therefore, (as Gordon mentioned) an index on two columns (symbol, timestamp) can satisfy your query, both the filtering and the ordering, quite efficiently. The query planner will do a random access operation on the index to the correct symbol and the starting timestamp, then read the index sequentially until the ending timestamp. That's efficient.

But, your SELECT * may be holding you back on performance. Why? If you used, for example, SELECT symbol, timestamp, cusip, name then you could create a so-called covering index on (symbol, timestamp, cusip, name). In that case, the entire query would be satisfied by scanning the index. That can be very efficient indeed.

Pro tip Avoid SELECT *, both for software stability and performance reasons.

Pro tip Don't add extra indexes to a table unless you know they will help particular queries. MySQL only uses a single index for each table in a query or subquery. Neither an index on just timestamp or just symbol will help much: MySQL still has to examine a lot of rows to satisfy your filtering criteria.

Upvotes: 0

Related Questions