How to optimize this query?

Question

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

Answer 1

I would suggest that you indexes to job_id assuming there are many events for the same job and event_date assuming that there are many events on the same date assuming that there are queries which use these columns for filters.

However see answer by @ypercube below which has a better explaination for indexes

PS: Updated to remove erroneous suggestions which are left below for reference:

b) In the query I can see that you are restricting by a date and time yet event_create_date is a date field, you can improve the comparison by using TO_DAYS(event_create_date) = TO_DAYS('2012-12-18 00:00:00')

c) Additional advice on indexes - add indexes to those columns with a lot of repetitive data, indexes are not very effective if the data varies widely ... (That is why @Rohan requested for more details on the data)

Answer 2

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.