CODEWITHSUNDEEP
sqlmysqlquery-optimizationinner-join
unbesiegbar
unbesiegbar

Reputation: 461

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 ?

Upvotes: 2

Views: 17643

Answers (3)

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

The join order is somewhat weird. I don't work with mysql so maybe it is just some unique way to join, but usually you join like:

FROM 
    a
    INNER JOIN b ON a.id1 = b.id2
    INNER JOIN c ON b.id3 = c.id4

Since you are using INNER JOIN this way you first filter out a with b joins and only then use the remaining join to filter out thus saving a lot of comparison actions. Imagine each table has 1 thousand rows. When you add c this becomes 1 million comparisons. Meanwhile with my example it would only be 1000 + 1000 comparisons instead of 1000 * 1000.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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)

Upvotes: 4

Sunil Tandon
Sunil Tandon

Reputation: 151

Try this:

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;

Upvotes: 2

Related Questions