When Case:MySQL uses left join, rewriting or to union can improve efficiency.
(1) before optimization: when using or, the execution time of SQL is 1.47s
Mysql > select e.emptive no on e.emp_no=d.emp_no where e.emp_no=32000 or d.from_date='1996 e.firstkeeper name on e.emp_no=d.emp_no where e.emp_no=32000 or d.from_date='1996 d.deptcircle no department d.fromcalendar date date on e.emp_no=d.emp_no where e.emp_no=32000 or d.from_date='1996-11-24' 58 rows in set (1.47 sec) mysql > desc select e.empkinnoree.firstdate from employees e left join dept_emp d on e.emp_no=d.emp_no where e.emp_no=32000 or d.from_date='1996-11-24' +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | e | NULL | ALL | PRIMARY | NULL | 299335 | | 100.00 | NULL | | 1 | SIMPLE | d | NULL | ref | PRIMARY | Emp_no | PRIMARY | 4 | employees.e.emp_no | 1 | 100.00 | Using where | +-+-- -+ 2 rows in set 1 warning (0.00 sec)
(2) after optimization: with the same result set, when using union, the SQL execution time is only 0.23s.
Mysql > select e.emproomnoree.firstproof name from employees e left join dept_emp d. Deptress date from employees e left join dept_emp d on e.emp_no=d.emp_no where d.from_date='1996-11-24' 58 rows in set (0.23 sec) mysql > desc select e.emproomnoree.firstdate from employees e left join dept_emp d on e.emp_no=d.emp_no where d.from_date='1996 d on e.emp_no=d.emp_no where d.from_date='1996-11-24' +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | PRIMARY | e | | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | PRIMARY | d | NULL | ref | PRIMARY | Emp_no | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 2 | UNION | d | NULL | ALL | PRIMARY Emp_no | NULL | 331143 | Using where | | 2 | UNION | e | NULL | eq_ref | PRIMARY | 4 | employees.d.emp_no | 1 | 100.00 | NULL | | NULL | UNION RESULT | | NULL | ALL | NULL | NULL | | NULL | NULL | Using temporary | +-+-- | -+ 5 rows in set 1 warning (0.00 sec)
