MySQL with JOIN not using index

Question

Problem with MySQL version 5.7.18. Earlier versions of MySQL behaves as supposed to.

Here are two tables. Table 1:

CREATE TABLE `test_events` (
  `id` int(11) NOT NULL,
  `event` int(11) DEFAULT '0',
  `manager` int(11) DEFAULT '0',
  `base_id` int(11) DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `client` int(11) DEFAULT '0',
  `event_time` datetime DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `test_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `client` (`client`),
  ADD KEY `event_time` (`event_time`),
  ADD KEY `manager` (`manager`),
  ADD KEY `base_id` (`base_id`),
  ADD KEY `create_time` (`create_time`);

And the second table:

CREATE TABLE `test_event_types` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `base` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `test_event_types`
  ADD PRIMARY KEY (`id`);

Let's try to select last event from base "314":

EXPLAIN  SELECT  `test_events`.`create_time`
    FROM  `test_events`
    LEFT JOIN  `test_event_types`
           ON ( `test_events`.`event` = `test_event_types`.`id` )
    WHERE  base = 314
    ORDER BY  `test_events`.`create_time` DESC
    LIMIT  1;

+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test_events      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 434928 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | test_event_types | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |     44 |     2.27 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

MySQL is not using index and reads the whole table. Without WHERE statement:

EXPLAIN  SELECT  `test_events`.`create_time`
    FROM  `test_events`
    LEFT JOIN  `test_event_types`
          ON ( `test_events`.`event` = `test_event_types`.`id` )
    ORDER BY  `test_events`.`create_time` DESC
    LIMIT  1;

+----+-------------+------------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table            | partitions | type   | possible_keys | key         | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+------------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | test_events      | NULL       | index  | NULL          | create_time | 4       | NULL                  |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | test_event_types | NULL       | eq_ref | PRIMARY       | PRIMARY     | 4       | m16.test_events.event |    1 |   100.00 | Using index |
+----+-------------+------------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

Now it uses index.

MySQL 5.5.55 uses index in both cases. Why is it so and what to do with it?

e4c5 e4c5 · Accepted Answer · 2017-05-08T03:09:32

I don't know the difference you are seeing in your previous and current installations but the servers behaviour makes sense.

SELECT  test_events.create_time  FROM  test_events  LEFT JOIN  test_event_types ON (  test_events.event =  test_event_types.id )  ORDER BY  test_events.create_time DESC LIMIT 1;

In this query you do not have a where clause but you are fetching one row only. And that's after sorting by create_time which happens to have an index. And that index can be used for sorting. But let's see the second query.

SELECT  test_events.create_time  FROM  test_events  LEFT JOIN  test_event_types ON (  test_events.event =  test_event_types.id ) WHERE base = 314 ORDER BY  test_events.create_time DESC LIMIT 1

You don't have an index on the base column. So no index can be used on that. To find the relevent records mysql has to do a table scan. Having identified the relevent rows, they need to be sorted. But in this case the query planner has decided that it's just not worth it to use the index on create_time

I see several problems with your setup, the first being not having and index on base as already mentioned. But why is base varchar? You appear to be storing integers in it.

ALTER TABLE test_events
  ADD PRIMARY KEY (id),
  ADD KEY client (client),
  ADD KEY event_time (event_time),
  ADD KEY manager (manager),
  ADD KEY base_id (base_id),
  ADD KEY create_time (create_time);

And making multiple indexes like this doesn't make much sense in mysql. That's because mysql can use only one index per table for queries. You would be far better off with one or two indexes. Possibly multi column indexes.

I think your ideal index would contain both create_time and event fields

MySQL with JOIN not using index

3 Answers