Description:
For an SQL using INNER JOIN, the explained plan shows that dynamic range could be used in the execution. The SQL is executed efficiently due to RANGE ACCESS being dynamically chosen for most prefix-joined rows during execution.
But when using LEFT JOIN, though the explained output shows the same plan with INNER JOIN, no RANGE ACCESS is chosen during execution, which causes poor performance.
How to repeat:
Load the data I've uploaded and execute the following SQL:
mysql-8.0.33 > EXPLAIN SELECT COUNT(0)
FROM t1
JOIN t2
ON t2.value = t1.name
OR t2.id = t1.sequence
WHERE (t1.status = 'NG')
OR (t1.status = 'FA')
OR (t1.status = 'IT');
+----+-------------+-------+------------+------+-------------------+------+---------+------+-------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+------+---------+------+-------+----------+------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | idx_name | NULL | NULL | NULL | 12857 | 27.10 | Using where |
| 1 | SIMPLE | t2 | NULL | ALL | PRIMARY,idx_value | NULL | NULL | NULL | 9239 | 19.00 | Range checked for each record (index map: 0x3) |
+----+-------------+-------+------------+------+-------------------+------+---------+------+-------+----------+------------------------------------------------+
mysql-8.0.33 > SELECT COUNT(0) FROM t1 JOIN t2 ON ((t2.value = t1.name) or (t2.id = t1.sequence)) WHERE ((t1.status = 'NG') or (t1.status = 'FA') or (t1.status = 'IT'));
+----------+
| COUNT(0) |
+----------+
| 9509 |
+----------+
1 row in set (0.32 sec)
As shown above, it takes only 0.32s to execute INNER JOIN. Now we change INNER JOIN to LEFT JOIN:
mysql-8.0.33 > EXPLAIN SELECT COUNT(0)
FROM t1
LEFT JOIN t2
ON t2.value = t1.name
OR t2.id = t1.sequence
WHERE (t1.status = 'NG')
OR (t1.status = 'FA')
OR (t1.status = 'IT');
+----+-------------+-------+------------+------+-------------------+------+---------+------+-------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+------+---------+------+-------+----------+------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 12857 | 27.10 | Using where |
| 1 | SIMPLE | t2 | NULL | ALL | PRIMARY,idx_value | NULL | NULL | NULL | 9239 | 100.00 | Range checked for each record (index map: 0x3) |
+----+-------------+-------+------------+------+-------------------+------+---------+------+-------+----------+------------------------------------------------+
mysql-8.0.33 > SELECT COUNT(0) FROM t1 LEFT JOIN t2 ON ((t2.value = t1.name) or (t2.id = t1.sequence)) WHERE ((t1.status = 'NG') or (t1.status = 'FA') or (t1.status = 'IT'));
+----------+
| COUNT(0) |
+----------+
| 9517 |
+----------+
1 row in set (29.69 sec)
For the same plan, it takes 29.69s to finish execution for LEFT JOIN. It's weird.
Suggested fix:
The reason is that, for LEFT JOIN the original JOIN condition has been wrapped with Item_func_trig_cond at the execution stage. Function get_mm_tree() doesn't deal with this type of Item properly and doesn't construct the tree. So it won't try RANGE ACCESS.
We can deal with it in get_mm_tree() as the following patch shows. After this patch, the LEFT JOIN will execute as efficiently as INNER JOIN.
mysql-8.0.33-with-patch > EXPLAIN SELECT COUNT(0) FROM t1 LEFT JOIN t2 ON ((t2.value = t1.name) or (t2.id = t1.sequence)) WHERE ((t1.status = 'NG') or (t1.status = 'FA') or (t1.status = 'IT'));
+----+-------------+-------+------------+------+-------------------+------+---------+------+-------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+------+---------+------+-------+----------+------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 12857 | 27.10 | Using where |
| 1 | SIMPLE | t2 | NULL | ALL | PRIMARY,idx_value | NULL | NULL | NULL | 9239 | 100.00 | Range checked for each record (index map: 0x3) |
+----+-------------+-------+------------+------+-------------------+------+---------+------+-------+----------+------------------------------------------------+
mysql-8.0.33-with-patch > SELECT COUNT(0) FROM t1 LEFT JOIN t2 ON ((t2.value = t1.name) or (t2.id = t1.sequence)) WHERE ((t1.status = 'NG') or (t1.status = 'FA') or (t1.status = 'IT'));
+----------+
| COUNT(0) |
+----------+
| 9517 |
+----------+
1 row in set (0.28 sec)
