Mysql Indexing problem with multiple 'or' and 'and'

I've a large datab开发者_如何转开发ase which is being used to record all the events occurring. It's like an ticketing system. Now, since I stored data with different status to know the action on the certain ticket in same column I've to use multiple 'or' in an statement to know the current status in the ticket.

For example: 1 is for a ticket opened, 2 for acknowledgement, 3 for event closed. Now the query to select all events with 1,2,3 would be:

SELECT * 
  FROM tbl_name 
 WHERE status IN (1, 2, 3) 
   AND event_id = 1;

I've created indexes for the id field, and another index event_status for event_id and status field.

Now, when I run EXPLAIN on this query it doesn't use event_status index rather it uses other existing index like event_status_dept which consist of event_id, status and department.

If I use only two fields in IN i.e 'IN (1,2)' statement, it uses the event_status index otherwise it uses the other index i.e event_status_dept. I don't know what is wrong with my statement.

---

I don't think anything is wrong with your query. The optimizer uses the best index it can find according to the conditions in the query and the statistics it holds. An index is not effective if more than a certain percent of the records satisfy the condition.

Example: If the optimizer's statistics say that only 5% of the events in the table are of the types 1,2, this would be an effective index and it will use it. But if 70% of the events are of types 1,2,3, this index is not effective and the optimizer may use another index or no index at all.

---

Using multiple indexes per table access is generally very inefficient:

http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/index-merge-performance

A concatenated index, like yours on event_id, status, department is the better solution.

However, MySQL has some kind of Index-Merge:

http://dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html