为查询设置mysql索引

What indexes i have to set for a query like this? thanks

对于这样的查询，需要设置哪些索引?谢谢

SELECT distinct event_dates.* FROM `event_dates` 
INNER JOIN `events` ON `events`.id = `event_dates`.event_id 
INNER JOIN `cores` ON `cores`.resource_id = `events`.id AND cores.resource_type = 'Event' 
INNER JOIN `cores_kinds` ON `cores_kinds`.core_id = `cores`.id 
INNER JOIN `kinds` ON `kinds`.id = `cores_kinds`.kind_id 
WHERE (((super_kind_en IN ('party','cinema and theater')) 
AND (day >= '2010-07-17' AND day <= '2010-08-16')) 
AND (cores.searchable like '%p%')) 
ORDER BY day, cores.vote desc LIMIT 0, 30

2 个解决方案

#1

The general rule of thumb is to have indexes on columns on which you filter your result set. In other words, columns included in the WHERE clause and columns on which you perform joins.

一般的经验法则是在你过滤结果集的列上有索引，也就是说，在你执行连接的WHERE子句和列中包含的列。

For a specific query, you might want to refer to the explain plan of the query. It will show you how MySQL executes the query, so can set up your indexes accordingly: http://dev.mysql.com/doc/refman/5.0/en/explain.html

对于特定的查询，您可能希望引用查询的解释计划。它将向您展示MySQL如何执行查询，因此可以相应地设置索引:http://dev.mysql.com/doc/refman/5.0/en/explain.html。

#2

I would set them on

我会让他们穿上

events.id
event_dates.event_id 
cores.resource_id
events.id
cores.resource_type
cores_kinds.core_id
cores.id 
kinds.id
cores_kinds.kind_id
super_kind_en
day
cores.searchable
cores.vote

as they all appear in either one of you JOIN, WHERE or ORDER BY clauses.

因为它们都出现在您的任何一个加入，在哪里或按子句顺序。

#1