Announcement

Announcement Module
Collapse
No announcement yet.

INNER JOIN Optimization

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • INNER JOIN Optimization

    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.

    Comment

    Working...
    X