When Case:MySQL uses in tape queries, it is best not to use union or union all for subqueries
When MySQL uses in tape queries, do not use union or union all for subqueries
Especially when the external table is large, never use in with union, because once union is used in the subquery, dependent subquery will occur in the execution plan.
We have used similar situations in production, resulting in poor execution efficiency of SQL. For the sake of production security and privacy, the following example is demonstrated with a test table with the same principle.
Give an example
(1) when using in and union, the s table is used as the external table, the full table is scanned, there are 260w rows, and the execution is more than 20 seconds.
Mysql > select s. * from salaries s where s.emp_no in (select emp_no from employees e where. Firstborn nameplate Georgia 'union all select emp_no from employees e where e.hire_date='1992-12-18'); 2718 rows in set (21.14 sec) mysql > desc select s. * from salaries s where s.emp_no in (select emp_no from employees e where. Firstborn nameplate Georgia 'union all select emp_no from employees e where e.hire_date='1992-12-18') +- +-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | PRIMARY | s | NULL | ALL | NULL | 2612229 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | e | | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 10.00 | Using where | 3 | DEPENDENT UNION | e | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 10.00 | Using where | +- -+ 3 rows in set 1 warning (0.00 sec)
(2) you can use join to transform, and then look at the appearance of the e table of the execution plan, and the competitive retrieval of the s table, which takes only 0.32 seconds to execute, which greatly improves the efficiency.
Mysql > select s. * from salaries s join (select emp_no from employees e where. Firstborn nameplate Georgia 'union all select emp_no from employees e where e.hire_date='1992-12-18') e on s.emp_no=e.emp_no 2718 rows in set (0.32 sec) mysql > desc select s. * from salaries s join (select emp_no from employees e where e.firstkeeper union all select emp_no from employees e where e.hire_date='1992-12-18') e on s.emp_no=e.emp_no +- +-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -- + | 1 | PRIMARY | | NULL | ALL | NULL | 59866 | 100.00 | NULL | | 1 | PRIMARY | s | | NULL | ref | PRIMARY | Emp_no | PRIMARY | 4 | e.emp_no | 9 | 100.00 | NULL | 2 | DERIVED | e | NULL | ALL | NULL | 299335 | Using where | 3 | UNION | e | NULL | 299335 | | | Using where | +-- +-- | -+-+ 4 rows in set 1 warning (0.00 sec)
