Why is MySQL showing index_merge on this query?

Question

I have what seems like a fairly simple table structure, however MySQL is defaulting to a less than optimal index_merge on a simple query.

Here's the table structure:

CREATE TABLE IF NOT EXISTS `event_log` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) DEFAULT NULL,
  `location_id` int(10) DEFAULT NULL,
  `object_id` int(5) DEFAULT NULL,
  `action_id` int(5) DEFAULT NULL,
  `date_event` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `user_id` (`user_id`),
  KEY `date_event` (`date_event`),
  KEY `action_id` (`action_id`),
  KEY `object_id` (`object_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

EXPLAIN on a basic SELECT query

EXPLAIN SELECT date_event
FROM event_log
WHERE user_id =123
AND object_id =456
AND location_id =789 

Returns this:

select_type  table     type         possible_keys       key                 key_len     ref     rows    Extra
SIMPLE       event_log index_merge  user_id,object_id   object_id,user_id   5,5         NULL    27      Using intersect(object_id,user_id); Using where

Here's the Extra bit, for easier reading:

Using intersect(object_id,user_id); Using where

Why is MySQL not using standard indexes on this query? Why is it intersecting user_id and object_id?

Answer 1

The most effective index for the query is a composite index that includes all three fields, for example: (object_id, user_id, location_id). Since there is no such index, MySQL does its best to get most of the information from existing indexes.