How to optimize this query?
Your query has 5 equality conditions in 5 different columns:

select * 
from t_event 
where create_user_id=7 
  and event_create_date = '2012-12-18 00:00:00'  
  and event_type_cd = 11 
  and domain_id = 602 
  and job_id =1 

limit 1 ;                  -- why is this for?




Now you have 2 indexes (domain_id_event_type_cd and event_user_FK), which cover only 3 of the 5 column/conditions of the query.

So the plan that the optimizer chooses is an index_merge operation, e.g. it uses the two indexes to find rows that match the 2 and 1 conditions respectively, then intersects them (Using intersect) to find the common rows and then has to do an additional search on the table (Using where) for the rest 2 conditions that are not covered by the indices.



You could add a compound index on all those columns. The order does not matter at all for this query, it matters for other queries you may have and whether it could be used for them:

ALTER TABLE t_event 
    ADD INDEX i5
        (domain_id, event_type_cd, create_user_id, event_create_date, job_id) ;


Then the Explain would show only Using index and only the above index would be used.