How to optimize this query? Query:

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


Table structure:

mysql&gt; show create table t_event\G
*************************** 1. row ***************************
       Table: t_event
Create Table: CREATE TABLE `t_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_user_id` int(11) DEFAULT NULL,
  `event_create_date` date DEFAULT NULL,
  `event_type_cd` int(11) NOT NULL,
  `event_desc` varchar(512) NOT NULL,
  `IsGlobalEvent` int(2) DEFAULT NULL,
  `event_start_date` datetime NOT NULL,
  `event_end_date` datetime NOT NULL,
  `job_id` int(11) DEFAULT NULL,
  `domain_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `event_user_FK` (`create_user_id`),
  KEY `domain_id_event_type_cd` (`domain_id`,`event_type_cd`)
) ENGINE=InnoDB AUTO_INCREMENT=8095673 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)


Explain:

+----+-------------+---------+-------------+---------------------------------------+---------------------------------------+---------+------+-------+---------------------------------------------------------------------+
| id | select_type | table   | type        | possible_keys                         | key                                   | key_len | ref  | rows  | Extra                                                               |
+----+-------------+---------+-------------+---------------------------------------+---------------------------------------+---------+------+-------+---------------------------------------------------------------------+
|  1 | SIMPLE      | t_event | index_merge | event_user_FK,domain_id_event_type_cd | domain_id_event_type_cd,event_user_FK | 8,5     | NULL | 14669 | Using intersect(domain_id_event_type_cd,event_user_FK); Using where |
+----+-------------+---------+-------------+---------------------------------------+---------------------------------------+---------+------+-------+---------------------------------------------------------------------+


How can I optimize this query? It has indexes but is taking too long to execute.
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.