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?

Favata

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.

Abad

Is it bad for performance? Do I make composited with the individal Index? – Tarn

@toleo, in a simple query like this mysql can guess that it can merge indexes and use them, but more frequently it doesn't and it is better to have a good index of the required columns. The performance depends on many factors. An index allows the RDBMS to walk through a sorted and smaller subset of the data than the whole table, which is usually faster, but the index consumes memory, which might affect performance in other ways. – Abad

@Abad So in my case Here:Columns = [1, 2, 3, 4, 5] All of them used Indvidually and together As the user pleases, I made the following keys that gave me the index_merge Keys = [1, 2, 3, 4, 5], Do I keep it like that or go with Keys = [1, 2, 3, 4, 5, 12, 13, 14, 15, 23, 24, 25, 34, 35, 45,~~] And so on? I find having the index_merge is better, But what do you recommend? – Tarn