Wrong execution plan in MySQL 8.0 when using JOIN in WHERE NOT IN clause

Question

After a migration from MySQL 5.7 to MySQL 8.0.34, we have a very strange behavior with a query when semijoin is on in the optimizer_switch.

1. The problematic query:

SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
 SELECT s4_.id
 FROM emails_history i5_
 INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
 WHERE i5_.rule_id IN (1517676 , 1517677)
)

In this case this is the execution plan:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	s0_	NULL	index	NULL	stores_shoppers_store_id_idx	4	NULL	361627391	100.00	Using index
1	SIMPLE	NULL	eq_ref	<auto_distinct_key>	<auto_distinct_key>	5	remarkety_prod.s0_.id	1	100.00	Using where; Not exists
2	MATERIALIZED	i5_	invalid_dates,p2022m03,...	ALL	emails_history_rule_id_idx	NULL	NULL	NULL	7526556120	100.00	Using where
2	MATERIALIZED	s4_	NULL	eq_ref	PRIMARY	PRIMARY	4	remarkety_prod.i5_.shopper_id	1	100.00	Using index

The problem is that the i5_ JOIN type is ALL, no index is used in the JOIN.

2. With only one value in the IN clause:

If the IN clause in the query includes only one value:

SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
 SELECT s4_.id
 FROM emails_history i5_
 INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
 WHERE i5_.rule_id IN (1517676) -- only one value
)

The execution plan becomes:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	s0_	NULL	index	NULL	stores_shoppers_store_id_idx	4	NULL	361632497	100.00	Using index
1	SIMPLE	NULL	eq_ref	<auto_distinct_key>	<auto_distinct_key>	5	remarkety_prod.s0_.id	1	100.00	Using where; Not exists
2	MATERIALIZED	i5_	invalid_dates,p2022m03,...	ref	emails_history_rule_id_idx	emails_history_rule_id_idx	5	const	56944	100.00	NULL
2	MATERIALIZED	s4_	NULL	eq_ref	PRIMARY	PRIMARY	4	remarkety_prod.i5_.shopper_id	1	100.00	Using index

In this case, the JOIN uses an index but the execution plan is still different from the execution plan we have in MySQL 5.7

3. Using i5_.shopper_id instead of s4_.id (which is supposed to be identical)

SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
 SELECT i5_.shopper_id -- using i5_.shopper_id instead of s4_.id
 FROM emails_history i5_
 INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
 WHERE i5_.rule_id IN (1517676 , 1517677)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	s0_	NULL	index	NULL	stores_shoppers_store_id_idx	4	NULL	361632516	100.00	Using where; Using index
2	SUBQUERY	i5_	invalid_dates,p2022m03,...	range	emails_history_rule_id_idx	emails_history_rule_id_idx	5	NULL	56945	100.00	Using index condition; Using where
2	SUBQUERY	s4_	NULL	eq_ref	PRIMARY	PRIMARY	4	remarkety_prod.i5_.shopper_id	1	100.00	Using index

In this case, the execution plan is the same as we had in MySQL 5.7

4. With semijoin=off

SET optimizer_switch = 'semijoin=off';
SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
 SELECT s4_.id
 FROM emails_history i5_
 INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
 WHERE i5_.rule_id IN (1517676 , 1517677)
)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	s0_	NULL	index	NULL	stores_shoppers_store_id_idx	4	NULL	361632550	100.00	Using where; Using index
2	SUBQUERY	i5_	invalid_dates,p2022m03,...	range	emails_history_rule_id_idx	emails_history_rule_id_idx	5	NULL	56945	100.00	Using index condition; Using where
2	SUBQUERY	s4_	NULL	eq_ref	PRIMARY	PRIMARY	4	remarkety_prod.i5_.shopper_id	1	100.00	Using index

Same execution plan as before.

Questions:

Can anyone explain to me what's going on ?
Is that a bug in the optimizer ?
Why the join doesn't use index in the first query ?

• Rick James: What indexes do you have? Did you try any EXISTS yourself?

Answer 1

Problem with optimizer_switch -- File a bug report at bugs.mysql.com

Recommend adding these indexes:

emails_history: INDEX(shopper_id, rule_id)
emails_history: INDEX(rule_id, shopper_id)

Some general tips:

• COUNT(x) tests x for being NOT NULL. You probably wanted COUNT(*).
• NOT IN ( SELECT ... ) has long been a poorly optimized construct. 5.7 got better; 8.0 tried to get it even better, but maybe not in this case.
• IN(1517676) is optimized as = 1517676, but IN (1517676 , 1517677) does not optimized as well. Sometimes a multi-item IN is best optimized with UNION.
• "semijoin" is probably a synonym of EXISTS(SELECT ...)

Performance thoughts

I don't understand the goal of the query, but here are some things I would try to do:

• Rewrite the query to avoid using stores_shoppers twice.
• Try NOT EXISTS and LEFT JOIN ... IS NULL
• Try ( SELECT ... rule_id = 123 ) UNION ALL ( SELECT ... rule_id = 123 )

• Arie S.: First of all, thank you for this clear answer. The query is a part of a much larger query generated by doctrine, I only posted this part to show the error we are encountering. That's why the goal is not really clear here. I followed your advice and rewrote the query without using stores_shoppers a second time and the problem was solved.