CODEWITHSUNDEEP
mysql
Huang Yen-Chieh
Huang Yen-Chieh

Reputation: 635

Is it faster to use two separate SQL statements or one SQL statement with JOIN?

I'm in doubt about the performance by "two or more individual SQL commands" or "1 but using 'join' SQL command".

Here is the thing:

For example, I have two tables: post, user

post has a user_id indicating this post is issued by some user in user

and I'm going to extract both post and username.

I can either

SELECT * FROM `post` LEFT JOIN `user` on post.user_id = user.id WHERE 1

or 

$rows = $db->sql("SELEFT * FROM `post` WHERE 1")
for $row in $rows:
    $db->sql("SELECT * FROM `user` WHERE id = $row['user_id']")

I'm wondering which one will have better performance?

And what if I have 3 or more tables to join, which way will be better (from the performance perspective)?

Upvotes: 1

Views: 964

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753495

Note that you are not executing 2 queries. You are executing the first query once and the second query N times, where N is the number of rows returned by the first query. This is going to be vastly more expensive than letting the query optimizer deal with a single join query.

Database optimizers are carefully written by experts to minimize the cost of operations. If you try to second guess the optimizer by running the separate queries, you should lose, badly, every time - unless the optimizer has severely blown it. All else apart, the DBMS has to prepare the variations on the second statement each time it is invoked, instead of just once. The optimizer would have to be so diabolically wrong that it simply won't happen.

Upvotes: 3

doc_id
doc_id

Reputation: 1433

The LEFT JOIN is strongly recommended because otherwise putting a query inside a loop (as in the second example) will multiple the hit on your database, which is also a dangerous situation.

Upvotes: 3

Marc B
Marc B

Reputation: 360572

The join will almost always be better performance. Even if the data it retrieves is "large", you're still only performing a single query.

It's like buying groceries. Easier to drive to the store ONCE, fill up a cart, and come home with many different items, versus going to the store, buying one thing, coming home, going to the store again, buying another.

Each of those trips adds up and you'll end up spending more time driving than enjoying your groceries.

Upvotes: 5

Related Questions