Search code examples
sqlmysqlquery-optimizationinner-join

Sql query taking long time with inner join

I am supposed to write a query which requires joining 3 tables. The query designed by me works fine, but it takes a lot of time to execute.

SELECT v.LinkID,  r.SourcePort, r.DestPort, r.NoOfBytes, r.StartTime , r.EndTime, r.Direction, r.nFlows
FROM LINK_TBL v 
INNER JOIN NODEIF_TBL n 
INNER JOIN RAW_TBL r ON 
    r.RouterIP=n.ifipaddress 
    and n.NodeNumber=v.orinodenumber 
    and v.oriIfIndex=r.OriIfIndex;

Is there any issue w.r.t performance in this query ?

Solution

  • Try this one put the on conditions in the joins

    SELECT v.LinkID,  r.SourcePort, r.DestPort, r.NoOfBytes, r.StartTime , r.EndTime, r.Direction, r.nFlows
FROM LINK_TBL v 
INNER JOIN NODEIF_TBL n ON (n.NodeNumber=v.orinodenumber )
INNER JOIN RAW_TBL r ON (r.RouterIP=n.ifipaddress   and v.oriIfIndex=r.OriIfIndex)