mysql indexes to set for a query

Question

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

Answer 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.

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

Answer 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.