4

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?

Improve this question

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

That's wierd, but it works. If I change type of column "base" to integer AND add an index to this column, MySQL would start using index on "create_time" field. If I only add index or only change type to int - it behaves as before. Varchar was set for field "base" by mistake a while ago, but it was OK before the update to last version of MySQL. Thank you.
I prefer not to use indexes in tables that would never exceed 5-10 rows (except primary index). But I suppose that was a wrong strategy. Nevertheless, the problem remains with other JOINs to text field. In example above I could easily change varchar to integer, but i could not do so in other tables, where the field contains some text code.
I didn't know that this table had less than 10 rows. Then of course an index wouldn't be needed. but then mysql query planner has always been wierd
Table test_events has about 400000 rows, and table test_event_types - about 10.
Sure, your answer was helpful, but it is not solved yet. It works for this particular case, but there are some other cases, where i should indeed select varchar value (not '314', but 'PJSIP/197-000028f0' for example). And new version of MySQL behaves the same: it goes through all the rows in bigger table not using index. I think I should update my question to clarify this.
|
2

base = 314 with base VARCHAR... is a performance problem. Either put quotes around 314 or make base some integer type.

You appear not to need LEFT. If not, then do a plain JOIN so that the optimizer has the freedom to start with an INDEX(base), which is then missing and needed.

As for the differences between 5.5 and 5.6 and 5.7, there have been a number of Optimization changes; you may have encountered a regression. But I don't want to chase that until you have improved the query and indexes.

Improve this answer

2 Comments

I could use quotes and remove LEFT, but the result wold be the same. MySQL 5.7 not using indexes where it should to. Let's say it's not 314, but "z314"
@TarasBulgakov - With z314 you would have to use quotes.
0

I stumbled upon same scenario where MySQL was using table scan, instead of INDEX search.

This could be because of one of the reasons, mentioned in MySQL docs:

The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length.

mysql docs link

And when I checked EXPLAIN of MySQL query in production server with large number of rows, it used INDEX search as expected.

Its one of the MySQL optimizations, under the hood :)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.