MySQL - 我如何知道要使用哪些索引？

Question

我正在尝试向以下查询添加索引。

SELECT * FROM Messages t WHERE perspective_user=? and timestamp BETWEEN ? AND ? AND 
timestamp_added = 
(select max(timestamp_added) from Messages t2 
where 
t2.author = t.author AND
t2.body = t.body AND
t2.timestamp = t.timestamp AND
t2.timestamp_added <= ?
) AND convo_id IN(SELECT convo_id FROM Conversations WHERE perspective_user=? AND identity=? AND timestamp_added=?);

我的第一个想法是三个索引:一个关于perspective_user、timestamp、timestamp_added和convo_id，第二个索引关于author、body、timestamp和timestamp_added，最后一个索引关于perspective_user、identity和timestamp_added。然而，这不起作用，而且据我所知，执行时间是相同的。

如何向该查询添加索引，更一般地说，我如何知道将来如何正确添加索引？

Answer 1

要使用的索引？

1. Indexes depends on your needs query.
2. Mostly of indexes used is from on your where clause.
3. One best thing of indexes to use is your primary key.
4. Avoid creating to much indexes to your tables cause it could make your data become slower to your query.

查看此indexes tutorial了解更多详情。