Bug #94364 Optimizer does not use index on multiple join
Submitted: 16 Feb 2019 9:52 Modified: 22 Mar 2019 13:52
Reporter: Masaki Goto Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.7.25-log OS:Any
Assigned to: CPU Architecture:Any
Tags: INDEX, join, Optimizer

[16 Feb 2019 9:52] Masaki Goto
Description:
very simple left join on 3 tables.

I expect that optimizer use index on c.id, but not.

Actually I found this problem on larger system.
Since table c has over 80000 rows, the search query becomes very slow.

How to repeat:

------------------query-------------------
explain SELECT c.*, o.name FROM contract c 
LEFT JOIN user u ON(c.user=u.id) 
LEFT JOIN office o ON(u.office=o.id) 
ORDER BY c.id LIMIT 0, 1

------------------EXPLAIN result-------------------
id  select_type table   partitions  type    possible_keys   key key_len     ref   rows  filtered    Extra
1   SIMPLE      c       NULL      ALL       NULL          NULL      NULL    NULL    12  100     Using temporary; Using filesort
1   SIMPLE      u       NULL      ALL       PRIMARY       NULL      NULL    NULL     5  100     Using where; Using join buffer (Block Nested Loop)
1   SIMPLE      o       NULL      eq_ref    PRIMARY       PRIMARY      4    u.office    1   100 NULL

------------------MYSQL data-------------------
CREATE TABLE `contract` (
  `id` int(11) NOT NULL,
  `user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `contract` (`id`, `user`) VALUES
(1, 1),
(2, 3),
(3, 3),
(4, 2),
(5, 1),
(6, 3),
(7, 5),
(8, 3),
(9, 2),
(10, 1),
(11, 2),
(12, 3);

CREATE TABLE `office` (
  `id` int(11) NOT NULL,
  `name` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `office` (`id`, `name`) VALUES
(1, 'tokyo'),
(2, 'osaka'),
(3, 'kyoto'),
(4, 'fukushima'),
(5, 'hiroshima');

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `office` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `office`) VALUES
(1, 3),
(2, 2),
(3, 1),
(4, 4),
(5, 3);

ALTER TABLE `contract`  ADD PRIMARY KEY (`id`) USING BTREE;
ALTER TABLE `office`    ADD PRIMARY KEY (`id`) USING BTREE;
ALTER TABLE `user`      ADD PRIMARY KEY (`id`) USING BTREE;
ALTER TABLE `contract`  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `office`    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `user`      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
[20 Feb 2019 15:34] MySQL Verification Team
Hi,

Thank you very much for your bug report.

You are using 5.7.15, which is very, very old release of 5..7.

Could you please try 5.7.25 and let us know whether this problem persists.

Thanks in advance.
[21 Feb 2019 3:18] Masaki Goto
I tried with 5.7.25.
But result is same as 5.7.15.
[21 Feb 2019 13:52] MySQL Verification Team
Hi,

I think that what you see is the expected behaviour. Let me try to explain it to you briefly.

Your table 'contract' is the left-most table in the two-stop LEFT OUTER JOIN. Being the left-most, implies that no index of that table can be used for the resolution of JOIN.

Hence, the only way to use that index is in sorting. However, as we are dealing with two-step JOIN, a temporary table is needed to resolve the query. That means, that only temporary table has to be sorted and read from in order to return sorted results. So, since table 'contract' is not needed any more, there is no way to use that index for the resolution of the ORDER BY.

I hope that it is clear. You can try to force this index for sorting, but I think you will get 100 % same execution plan from the optimiser.
[22 Mar 2019 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".