CODEWITHSUNDEEP
mysqlperformanceoptimizationquery-optimization
pillarOfLight
pillarOfLight

Reputation: 8992

efficiently use OR statement in LEFT JOIN

so I have this query

SELECT * FROM b 
   LEFT JOIN n ON b.id = n.id 
   LEFT JOIN nr ON (b.pid <> 0 AND b.pid = nr.vid) 
        OR (b.pid = 0 AND n.vid = nr.vid)

But an EXPLAIN statement on that query would reveal that in order to execute the OR in the join, it needs to search through all the entries in the nr table even though nr.vid is the primary key of that table AND there are indices on b.pid and n.vid..

Any suggestion as to how to make this more efficient?

Explain Result

1, 'SIMPLE', 'b', 'ref', 'PRIMARY,r_rID', 'r_rID', '197', 'const,const', 48, 'Using where; Using temporary; Using filesort'
1, 'SIMPLE', 'n', 'eq_ref', 'PRIMARY,nst,nnc,ut,uid', 'PRIMARY', '4', 'b.nid', 1, 'Using where'
1, 'SIMPLE', 'nr', 'ALL', '', '', '', '', 335654, ''
1, 'SIMPLE', 'tn', 'ref', 'nid', 'nid', '4', 'n.nid', 1, ''

Upvotes: 2

Views: 88

Answers (1)

defuz
defuz

Reputation: 27621

I'm not sure, but it may be useful:

SELECT * FROM b 
   LEFT JOIN n ON b.id = n.id 
   LEFT JOIN nr as nr1 ON b.pid <> 0 AND b.pid = nr1.vid      
   LEFT JOIN nr as nr2 ON b.pid = 0 AND n.vid = nr2.vid

Maybe, mysql can't understand that your expressions mutually exclusive. In theory, this query should work equivalent to yours.

Upvotes: 2

Related Questions