CODEWITHSUNDEEP
mysqlperformance
SnareChops
SnareChops

Reputation: 13347

MySQL LEFT JOIN vs. 2 separate queries (Performance)

I have two tables:

++++++++++++++++++++++++++++++++++++
|              Games               |
++++++++++++++++++++++++++++++++++++
| ID |  Name  |    Description     |
++++++++++++++++++++++++++++++++++++
| 1  | Game 1 | A game description |
| 2  | Game 2 | And another        |
| 3  | Game 3 | And another        |
| .. |  ...   |       ...          |
++++++++++++++++++++++++++++++++++++

+++++++++++++++++++++++++++++++++++++++
|             GameReviews             |
+++++++++++++++++++++++++++++++++++++++
| ID |GameID|          Review         |
+++++++++++++++++++++++++++++++++++++++
| 1  |  1   |Review for game 1        |
| 2  |  1   |Another review for game 1|
| 3  |  1   |And another              |
| .. | ...  |         ...             |
+++++++++++++++++++++++++++++++++++++++

Option 1:

SELECT 
    Games.ID, 
    Games.Name,
    Games.Description, 
    GameReviews.ID, 
    GameReviews.Review 
FROM 
    GameReviews
LEFT JOIN
    Games
ON
    Games.ID = GameReviews.GameID
WHERE
    Games.ID=?

Option 2:

SELECT
    ID,
    Name,
    Description
FROM
    Games
WHERE
    ID=?

and then SELECT ID, Review FROM GameReviews WHERE GameID=?

Obviously query 1 would be "simpler" where it is less code to write, and the other would seem to logically be "easier" on the database as it only queries the Games table once. The question is when it really gets down to it is there really a difference in performance and efficiency?

Upvotes: 0

Views: 1989

Answers (2)

Brent Baisley
Brent Baisley

Reputation: 12721

The vast majority of the time option 1 would be the way to go. The performance difference between the two would not be measurable until you have a lot of data. Keep it simple.

Your example is also fairly basic. At scale, performance issues can start revealing themselves based on what fields are being filtered, joined and pulled. The ideal scenario is to only pull data that exists in indexes (particularly with InnoDB). That usually is not possible, but a strategy is to pull the actual data you need at the last possible moment. Which is sort of what option 2 would be doing.

At extreme scale, you don't want to do any joins in the database at all. Your "joins" would happen in code, minimizing data sent over the network. Go with option 1 until you start having performance issues, which may never happen.

Upvotes: 1

peterm
peterm

Reputation: 92785

Go with the option 1, that is exactly what RDBMSes are optimized for.
And it always better to hit a database once from the client than hit it repeatedly multiple times.

I don't believe that you will ever have so many games and reviews that it will make sense to go with option 2.

Upvotes: 1

Related Questions