Wrong Execution Plan in MySQL 8.0 with JOIN in WHERE NOT IN Clause

MySQLmysql-8.0optimization

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 ?

Best Answer

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 )
Related Question