How to Tune SQL Statement with Driving Path Control for MySQL?

The following is an example shows a SQL statement with two potential table join paths. “Employee to Department” and “Department to Employee” are potential driving paths which will be decided by MySQL SQL optimizer during SQL optimization stage.

select emp_id,emp_name,dpt_avg_salary
from employee ,department
where emp_dept=dpt_id

and emp_dept like 'A%'
and dpt_id like 'A%';​

Here the following is the query plan selected by MySQL SQL optimizer in Tosska proprietary tree format, it takes 59 seconds to finish.

The query plan looks reasonable that uses DEPARTMENT’s Primary Key (DPT_ID) to fetch DEPARTMENT table first and then nested loop EMPLOYEE table by EMP_DEPT index, the speed of this query plan depends on the size of EMPLOYEE table and the records distribution according to the EMP_DEPT code.

If we want to change the driving path of the query plan from EMPLOYEE to DEPARTMENT, let me add a ifnull(dpt_id,dpt_id) dummy function to disable the DPT_ID index search, so it can artificially add cost to condition search DEPARTMENT table first. It means that using EMP_DEPT index search cost is relative cheaper now, so EMPLOYEE to DEPARTMENT driving path is probably be selected by MySQL SQL optimizer in the following:

select emp_id,

emp_name,
dpt_avg_salary​

from employee,

department​

where emp_dept = dpt_id

and emp_dept like 'A%'
and ifnull(dpt_id,dpt_id) like 'A%'​

EMPLOYEE to DEPARTMENT driving path plan is generated by MySQL now and it takes only 18.8 seconds only to finish the query.

If we know that using the EMP_DEPT index is not that efficient due to the selectivity of “ like 'A%' “ condition may not high enough to utilize the index range scan. Let me add an additional ifnull(emp_dept,emp_dept) dummy function to disable the EMP_DEPT index range scan too in the following:

select emp_id,

emp_name,
dpt_avg_salary​

from employee,

department​

where emp_dept = dpt_id

and ifnull(emp_dept,emp_dept) like 'A%'
and ifnull(dpt_id,dpt_id) like 'A%'​

Now, MySQL use full table scan of EMPLOYEE table to nested loop DEPARTMENT table. The speed is further improved to 15 seconds now.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the best rewrite is around 4 times faster than the original SQL.
https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/