Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
which is the most selective where condition? you need to give more details about the nature of the data.. –  Rohan Dec 20 '12 at 5:28
add comment

2 Answers 2

up vote 2 down vote accepted

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)

share|improve this answer
    
The TO_DAYS(event_create_date) = TO_DAYS('2012-12-18 00:00:00') suggestion would not be an improvement, but the opposite. –  ypercube Dec 20 '12 at 7:30
    
And the (c) advice is totally wrong. –  ypercube Dec 20 '12 at 8:28
    
@ypercube My understanding is that the string comparison for dates is slower, also why is (c) wrong my understanding is that indexes are better for data that is repeated as they tend to be clustered together. Thanks –  ssmusoke Dec 20 '12 at 8:53
    
Regarding (b) the issue is sargability. Regarding (c) it is about selectivity. If the index does not cover the query then it will need to lookup the remaining columns from the base table. As the index gets less selective the cost of these additional lookups is such that it would be quicker to just scan the table entirely and not bother with the index. –  Martin Smith Dec 20 '12 at 10:15
1  
@ssmusoke - AFAIK once it is accepted the system doesn't allow you to delete it. You could flag it for a moderator to delete it or maybe look at improving it instead. –  Martin Smith Dec 20 '12 at 11:02
show 1 more comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.