MySQL with JOIN does not use index
Problem with MySQL version 5.7.18. Earlier versions of MySQL behave as expected.
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`);
Try to select the last event from the "314" database:
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 does not use an index and reads the entire table. Without WHERE clause:
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)
It is now using the index.
MySQL 5.5.55 uses an index in both cases. Why is this so and what to do with it?
source to share
I don't know the difference you see in previous and current installations, but the server behavior 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 don't have a where clause, but you are only selecting one row. And this is after the sort create_time
that has the index. And this index can be used for sorting. But let's look at 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 underlying column. Thus, no index can be used for this. To find the relevant records, mysql must perform a table scan. Having identified the corresponding lines, they need to be sorted. But in this case, the query planner decided that it was simply not worth using an index oncreate_time
I see several problems with your setup, the first one is not listed or pointed to base
, as already mentioned. But why varchar base? You seem 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 creating multiple indexes like this doesn't make much sense in mysql. This is because mysql can only use one index per table for queries. You will be much better off with one or two indices. Many column indexes are possible.
I think your ideal index would contain both create_time fields and events
source to share
base = 314
s base VARCHAR...
is a performance issue. Either put quotes around 314
or do base
some integer type.
You don't seem to need to LEFT
. If not, then JOIN
keep it simple so that the optimizer has the right to start with INDEX(base)
, which is then missing and needed.
Regarding the differences between 5.5 and 5.6 and 5.7, there have been a number of Optimization changes; you may have encountered regression. But I don't want to chase this until you improve your query and indexes.
source to share