LEFT JOIN vs. multiple SELECT statements

Question

I am working on someone else's PHP code and seeing this pattern over and over:

(pseudocode)

result = SELECT blah1, blah2, foreign_key FROM foo WHERE key=bar

if foreign_key > 0  
  other_result = SELECT something FROM foo2 WHERE key=foreign_key  
end

The code needs to branch if there is no related row in the other table, but couldn't this be done better by doing a LEFT JOIN in a single SELECT statement? Am I missing some performance benefit? Portability issue? Or am I just nitpicking?

Answer 1

This is definitely wrong. You are going over the wire a second time for no reason. DBs are very fast at their problem space. Joining tables is one of those and you'll see more of a performance degradation from the second query then the join. Unless your tablespace is hundreds of millions of records, this is not a good idea.

Answer 2

There is not enough information to really answer the question. I've worked on applications where decreasing the query count for one reason and increasing the query count for another reason both gave performance improvements. In the same application!

For certain combinations of table size, database configuration and how often the foreign table would be queried, doing the two queries can be much faster than a LEFT JOIN. But experience and testing is the only thing that will tell you that. MySQL with moderately large tables seems to be susceptable to this, IME. Performing three queries on one table can often be much faster than one query JOINing the three. I've seen speedups of an order of magnitude.

Answer 3

I'm with you - a single SQL would be better