Shulou(Shulou.com)06/01 Report--

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.