5

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?

a coder
  • 7,530
  • 20
  • 84
  • 131

1 Answers1

14

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.

newtover
  • 31,286
  • 11
  • 84
  • 89
  • Is it bad for performance? Do I make composited with the individal Index? – Toleo Feb 10 '18 at 17:12
  • @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. – newtover Feb 10 '18 at 19:31
  • @newtover 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? – Toleo Feb 11 '18 at 14:34