INNER JOIN Optimization

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I have some of the queries with INNER JOIN with multiple fields in WHERE caluse. I am wondering is that possible to improve query performance if we filter records first than apply equi-join ?

I tried by putting WHERE caluse up from equi-join. But EXPLAIN shows same plan as earlier. I thought if we filter records set first than apply equi join that would be much faster.

Please let me know with example. original query is as follows:

SELECT t1.empid,t1.empname,t2.deptname from employees t1
INNER JOIN departments t2
ON t1.deptid = t2.deptid
WHERE t2.deptname = ‘Sales’
AND t1.empname LIKE ‘p%’;

2

Use EXPLAIN EXTENDED and SHOW WARNINGS to see how the query optimizer interprets your query. In general the query optimizer is smart and transforms the query. You should be able to see that in action.