已建symbol和timestamp单列索引，如何优化耗时超5分钟的SQL查询？

Hey, let's work through this slow query problem together— I’ve dealt with similar time-range + filter issues on trading databases before, so here are some concrete steps you can take to speed things up:

• Swap individual indexes for a composite (joint) index
  Right now you have separate indexes on symbol and timestamp, but most databases (like MySQL, which is common for trading systems) can only efficiently use one single-column index per query. A composite index tailored to your exact WHERE clause will fix this:

  CREATE INDEX idx_symbol_timestamp ON trades(symbol, timestamp);
  

  This index first filters all rows matching 'ICX/BTC', then quickly narrows down to the timestamp range you specified—perfectly aligned with your query logic. If you don't actually need every column (i.e., you can replace SELECT * with specific columns), go a step further and make it a covering index by adding those columns to the end:

  CREATE INDEX idx_symbol_timestamp_covering ON trades(symbol, timestamp, price, amount);
  

  Covering indexes let the database pull all needed data directly from the index without hitting the main table, which cuts down on I/O time significantly.

• Verify the index is actually being used
  Sometimes the query optimizer might skip indexes due to outdated table statistics or edge cases. Run EXPLAIN to check what's happening:

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

  Look at the type column in the output: range or ref means the index is working. If you see ALL, that's a full table scan. Try updating table statistics first:

  ANALYZE TABLE trades;
  

  If that doesn't work, you can force the index as a temporary fix (though it's better to figure out why the optimizer is avoiding it long-term):

  SELECT * FROM trades FORCE INDEX(idx_symbol_timestamp) WHERE `symbol` = 'ICX/BTC' AND `timestamp` >= :since AND `timestamp` <= :until ORDER BY `timestamp` LIMIT 50000;
  

• Trim down your SELECT and adjust LIMIT
  SELECT * pulls every column, which wastes bandwidth and I/O if you don't need all of them. Replace it with only the columns your business logic requires. Also, LIMIT 50000 is returning a huge chunk of data—if your use case allows, split this into smaller paginated queries (e.g., LIMIT 1000 OFFSET 0, then OFFSET 1000, etc.) to reduce resource strain.

• Consider table partitioning for large datasets
  If your trades table has millions/billions of rows, partitioning by timestamp (e.g., monthly or quarterly partitions) will let the query only scan the relevant time partitions instead of the entire table. This can drastically reduce the amount of data the database needs to process.

• Check your time range scope
  If the :since and :until parameters cover a massive time span (like years of data), even with an index, you're still scanning a ton of rows. If possible, narrow the time range or split the query into smaller time chunks to process sequentially.

内容的提问来源于stack exchange，提问作者Alex108dev