title: MySQL with JOIN not using index
body: <p>Problem with MySQL version 5.7.18.  Earlier versions of MySQL behaves as supposed to.</p>
  
  <p>Here are two tables. Table 1:</p>
  
  <pre><code>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`);
  </code></pre>
  
  <p>And the second table:</p>
  
  <pre><code>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`);
  </code></pre>
  
  <p>Let's try to select last event from base "314":</p>
  
  <pre><code>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;
  </code></pre>
  
  <pre>
  +----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  | 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)
  </pre>
  
  <p>MySQL is not using index and reads the whole table.
  Without WHERE statement:</p>
  
  <pre><code>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;
  </code></pre>
  
  <pre>
  +----+-------------+------------------+------------+--------+---------------+-------------+---------+-----------------------+------+----------+-------------+
  | 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)
  
  </pre>
  
  <p>Now it uses index.</p>
  
  <p>MySQL 5.5.55 uses index in both cases. Why is it so and what to do with it?</p>