MySQL Bugs: #86205: optimizer index using select where column in select

Bug #86205	optimizer index using select where column in select
Submitted:	6 May 2017 23:13	Modified:	9 Jun 2017 11:48
Reporter:	mohamed atef	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6/5.7	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	improper index using

Description:
CREATE DATABASE db1 CHARSET 'utf8' collate 'utf8_unicode_ci';
use db1;
create table table1
(id_ bigint(20) unsigned not null,
store_en varchar(20) not null collate 'utf8_unicode_ci' ,
primary key(id_),
unique key store_entable1 (store_en) using btree
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

use db1;
create table table2
(id_ bigint(20) unsigned not null,
store_id bigint(20) unsigned not null ,
primary key(id_),
 key store_id_table2 (store_id) using btree,
 constraint foreign key store_id_table2_fk (store_id) references table1 (id_) on update cascade on delete restrict 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

use db1;
INSERT INTO `db1`.`table1` (`id_`, `store_en`) VALUES ('1', 'store1');
INSERT INTO `db1`.`table1` (`id_`, `store_en`) VALUES ('2', 'store2');
INSERT INTO `db1`.`table1` (`id_`, `store_en`) VALUES ('3', 'store3');
INSERT INTO `db1`.`table1` (`id_`, `store_en`) VALUES ('4', 'store4');
INSERT INTO `db1`.`table1` (`id_`, `store_en`) VALUES ('5', 'store5');
INSERT INTO `db1`.`table1` (`id_`, `store_en`) VALUES ('6', 'store6');

use db1;
INSERT INTO `db1`.`table2` (`id_`, `store_id`) VALUES ('1', '5');
INSERT INTO `db1`.`table2` (`id_`, `store_id`) VALUES ('2', '6');
INSERT INTO `db1`.`table2` (`id_`, `store_id`) VALUES ('3', '2');
INSERT INTO `db1`.`table2` (`id_`, `store_id`) VALUES ('4', '3');
INSERT INTO `db1`.`table2` (`id_`, `store_id`) VALUES ('5', '5');
INSERT INTO `db1`.`table2` (`id_`, `store_id`) VALUES ('6', '1');

explain extended SELECT * FROM db1.table1 where id_ in (select store_id from db1.table2 );
it will return 3 rows
it will use this key as primay -> store_entable1
and materialize
this in mysql 5.7.18
and mysql 5.7.17

UPDATE `db1`.`table2` SET `store_id`='1' WHERE `id_`='3';
UPDATE `db1`.`table2` SET `store_id`='1' WHERE `id_`='4';
UPDATE `db1`.`table2` SET `store_id`='1' WHERE `id_`='1';
UPDATE `db1`.`table2` SET `store_id`='1' WHERE `id_`='5';
UPDATE `db1`.`table2` SET `store_id`='1' WHERE `id_`='2';

but when update column store_id to be the same in all rows in table2
in mysql 5.7.18 explain select will return the same rows with the same index
but in mysql 5.7.17 
it will use PRIMARY as key
and return 2 rows
files attached

How to repeat:
improper index using

Suggested fix:
i don`t know

mysql 5.7.18 before and after update rows explain select

Attachment: 5.7.18 explain.html (text/html), 2.32 KiB.

explain in mysql 5.7.17 after update

Attachment: 5.7.17 explain after update.html (text/html), 1.77 KiB.

Thank you for the bug report. 5.5/8.0 (return 2 rows) behaves different than 5.6/5.7 (return 3 rows).

Posted by developer:
 
The query plans differ in what semijoin strategy is used.  (MatLookup vs LooseScan)
The choice of semijoin strategy is cost-based.
If data is changed, a different strategy may be better suited.
However, statistics are not updated immediately, so it may be that just after the update (and for old connections) the old plan will still be used.

A specific example where it is shown that the selected plan is the wrong choice will be required if we should consider changing the current cost model.
In other words, do you have any indication that either of the plans are a bad choice for the given query and data?

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".