How to Tune SQL statement with Transitive Dependency Improvement for MySQL?

The following is an example shows a SQL statement with two conditions “emp_dept=dpt_id and emp_dept<'L'” .

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 is the query plan of this SQL in Tosska proprietary tree format, it takes 8.84 seconds to finish.

The query plan looks reasonable that shows a full table scan of DEPARTMENT to nested-loop EMPLOYEE table, the records in EMPLOYEE table being nested-loop must satisfy with the condition “emp_id<1500000” and the corresponding index EMPS_SALARY_INX is also used. Due to the number of records in the first driving table in a Nested Loop Join is very critical to the join performance, we should find a way to narrow down the number of result records of DEPARTMENT table before it is used to nested-loop EMPLOYEE table.

As the conditions “emp_dept=dpt_id and emp_dept<'L'”, it implies that “dpt_id < 'L'” is also true, let me add this extra condition to the SQL, it helps MySQL SQL optimizer to make a better decision with more information provided by the new SQL syntax, this technique is especially useful for MySQL database.

Remark:
Oracle or MS SQL Server are doing very good on their internal Transitive Dependency Improvement in their SQL optimizer already, so this technique may not work for Oracle and MS SQL Server.


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

Let’s see the DEPARTMENT is now being filtered by the new condition “dpt_id < 'L' “ with an index range scan. You can see the estimated Rows 401 of DEPARTMENT table is now being trimmed down to 176. The rewritten SQL now takes only 3.8 seconds with such a simple change in syntax.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that this rewrite is more than 2 times faster than the original SQL with such an easy change in the syntax.
https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/