mysql - Mysql query takes too much time with Index

Question

My query takes too much time with index. How to create a good index for my Mysql query?

Query

SELECT `id`, `time`, `volume`, `candle` FROM `forex_history` 
WHERE 
   period='30m' AND 
   id IN ('40','1817') 
   ORDER BY `time` DESC 
LIMIT 600;

95 million rows in the table

Slow query log:

# Query_time: 3.801843  Lock_time: 0.000076  Rows_sent: 600  Rows_examined: 10966
# Rows_affected: 0  Bytes_sent: 49296

10% queries are slow. Query takes 0.2 Sec to 30 Sec

Table schema:

Execution plan:

标签： mysql

Answer 1

我可以建议以下索引：

CREATE INDEX idx ON forex_history (period, time, volume, candle);
-- add id if not using InnoDB

该索引将涵盖整个WHERE子句以及SELECT.

您可以尝试其他两个版本：

(period, id, time, volume, candle)
(period, id, time)