How to Tune SQL Statements with Rewrite and Hints Injection for MySQL?

There are some SQL statements with performance problem have to be tuned by SQL syntax rewrite and Hints injection, it is a little bit difficult for SQL tuning newcomers to master this technique. Developers not only have to understand the relationship between SQL syntax and the final query plan generation but have to understand the usage of optimizer hints and its limitations. Sometimes these two tuning techniques application will affect each other in a complex SQL statement.

Here is a simple example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables.

select * from employee,department
where emp_dept=dpt_id

and emp_dept<'L'
and emp_id<1500000
and emp_salary= dpt_avg_salary​

order by dpt_avg_salary

Here the following are the query plans of this SQL, it takes 7.7 seconds to finish. The query shows a “Full Table Scan Department” and nested loop Employee table with a Non-Unique Key Lookup EMPS_SALARY_INX.


You can see that this SQL cannot utilize index scan even though the dpt_dept is an indexed field. It is because the condition emp_dept<'L' is not explicitly induced the condition dpt_id < 'L' although emp_dept=dpt_id is also listed in the where clause.

To enable the index search of Department table, I explicitly add a condition dpt_id < 'L' to the SQL statement as the following:

select *
from employee,
department
where emp_dept = dpt_id

and dpt_id < 'L'
and emp_dept < 'L'
and emp_id < 1500000
and emp_salary = dpt_avg_salary​

order by dpt_avg_salary

Here is the query plan of the rewritten SQL and the execution time is reduced to 3.4 seconds. The new query plan shows that an Index Range Scan is used for the Department table and nested loop Employee table.

You may find that the nested loop to Employee by EMPS_SALARY_INX lookup may result into a lot of random access to the Employee table. Let me add a BKA hint to ask MySQL to use ‘Batched Key Access’ to join the two tables.

select /*+ QB_NAME(QB1) BKA(`employee`@QB1) */ *
from employee,
department
where emp_dept = dpt_id

and dpt_id < 'L'
and emp_dept < 'L'
and emp_id < 1500000
and emp_salary = dpt_avg_salary​

order by dpt_avg_salary

The new query plan shows a Batched Key Access is used to join Department and Employee tables, you can BAK information from MySQL manual for details, the new plan takes only 1.99 seconds and it is more than 3 times better than the original SQL syntax.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/