MySQL query takes longer with indices?

I have a MySQL InnoDB table, events, with 3 rows:

event_id  ---> int
start     ---> long
end       ---> long

I have written a simple query for getting all events within some range that conflict with another event:

select a.* from events a, events b 
where a.event_id != b.event_id and 
      a.start < b.end and 
      b.start < a.end

The table has ~10K rows and was taking ~2 seconds to execute. While trying to tweak with the table to increase performance, I added indices on the start and end columns. 开发者_StackOverflow For some reason, this dramatically slowed performance by a factor of 5x. Does anyone know, or have any ideas, why adding these indices would hurt performance that bad?

MySQL looks at the columns involved and is mislead into thinking that the indexes will help improve the query. But the problem is that the indexes solve the start-end portions but require resolution back to the record-ids to resolve the a.event_id != b.event_id, which in itself is an expensive operation.

Not having the index forces a cross join and filter, which although may balloon up to a large number of temporary records, is a much simpler and straightforward implementation.

If your query is bound by some range on a or b against start-end, and/or the table is larger than 10k, it could become a very different picture.

If you need to keep the index, you can force the particular query (if you know this helps) to ignore the index:

select a.*
from events a ignore index (index1)
cross join events b ignore index (index1)
where a.event_id != b.event_id and 
      a.start < b.end and 
      b.start < a.end

Assuming the index is named index1. In any case it is always helpful to see what MySQL is doing in each case, by adding "EXPLAIN" before the query to show how MySQL is going about collecting the results (without index, with index, with index but ignored)