JOIN queries vs multiple queries

This is way too vague to give you an answer relevant to your specific case. It depends on a lot of things. Jeff Atwood (founder of this site) actually wrote about this. For the most part, though, if you have the right indexes and you properly do your JOINs it is usually going to be faster to do 1 trip than several.

This question is old, but is missing some benchmarks. I benchmarked JOIN against its 2 competitors:

• N+1 queries
• 2 queries, the second one using a WHERE IN(...) or equivalent

The result is clear: on MySQL, JOIN is much faster. N+1 queries can drop the performance of an application drastically:

That is, unless you select a lot of records that point to a very small number of distinct, foreign records. Here is a benchmark for the extreme case:

This is very unlikely to happen in a typical application, unless you're joining a -to-many relationship, in which case the foreign key is on the other table, and you're duplicating the main table data many times.

Takeaway:

• For *-to-one relationships, always use JOIN
• For *-to-many relationships, a second query might be faster

See my article on Medium for more information.

Construct both separate queries and joins, then time each of them -- nothing helps more than real-world numbers.

Then even better -- add "EXPLAIN" to the beginning of each query. This will tell you how many subqueries MySQL is using to answer your request for data, and how many rows scanned for each query.

Depending on the complexity for the database compared to developer complexity, it may be simpler to do many SELECT calls.

Try running some database statistics against both the JOIN and the multiple SELECTS. See if in your environment the JOIN is faster/slower than the SELECT.

Then again, if changing it to a JOIN would mean an extra day/week/month of dev work, I'd stick with multiple SELECTs

Cheers,

BLT

Whether you should use a join is first and foremost about whether a join makes sense. Only at that point is performance even something to be considered, as nearly all other cases will result in significantly worse performance.

Performance differences will largely be tied to how related the info you're querying for is. Joins work, and they're fast when the data is related and you index stuff correctly, but they do often result in some redundancy and sometimes more results than needed. And if your data sets are not directly related, sticking them in a single query will result in what's called a Cartesian product (basically, all possible combinations of rows), which is almost never what you want.

This is often caused by many-to-one-to-many relationships. For example, HoldOffHunger's answer mentioned a single query for posts, tags, and comments. Comments are related to a post, as are tags...but tags are unrelated to comments.

+------------+     +---------+     +---------+
|  comment   |     |   post  |     |  tag    |
|------------|*   1|---------|1   *|---------|
| post_id    |-----| post_id |-----| post_id |
| comment_id |     | ...     |     | tag_id  |
| user_id    |     |         |     | ...     |
| ...        |     |         |     | ...     |
+------------+     +---------+     +---------+

In this case, it is unambiguously better for this to be at least two separate queries. If you try to join tags and comments, because there's no direct relation between the two, you end up with every possible combination of tag and comment. many * many == manymany. Aside from that, since posts and tags are unrelated, you can do those two queries in parallel, leading to potential gain.

Let's consider a different scenario, though: You want the comments attached to a post, and the commenters' contact info.

 +----------+     +------------+     +---------+
 |   user   |     |  comment   |     |   post  |
 |----------|1   *|------------|*   1|---------|
 | user_id  |-----| post_id    |-----| post_id |
 | username |     | user_id    |     | ...     |
 | ...      |     | ...        |     +---------+
 +----------+     +------------+

This is where you should consider a join. Aside from being a much more natural query, most database systems (including MySQL) have lots of smart people put lots of hard work into optimizing queries just like it. For separate queries, since each query depends on the results of the previous one, the queries can't be done in parallel, and the total time becomes not just the actual execute time of the queries, but also the time spent fetching results, sifting through them for IDs for the next query, linking rows together, etc.

Yes, one query using JOINS would be quicker. Although without knowing the relationships of the tables you are querying, the size of your dataset, or where the primary keys are, it's almost impossible to say how much faster.

Why not test both scenarios out, then you'll know for sure...