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 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.

2 @Paolo: could you plz fix the link. It send me to the blog home page not to the article. I can't find it.

Thanks! – Marco Demaio Mar 18 '10 at 10:02.

I actually came to this question looking for an answer myself, and after reading the given answers I can only agree that the best way to compare DB queries performance is to get real-world numbers because there are just to many variables to be taken into account BUT, I also think that comparing the numbers between them leads to no good in almost all cases. What I mean is that the numbers should always be compared with an acceptable number and definitely not compared with each other. I can understand if one way of querying takes say 0.02 seconds and the other one takes 20 seconds, that's an enormous difference.

But what if one way of querying takes 0.0000000002 seconds, and the other one takes 0.0000002 seconds? In both cases one way is a whopping 1000 times faster than the other one, but how "whopping" is it really in the second case? Bottom line as I personally see it: if it performs well, go for the easy solution.As long as you have a team manager, your code will get trolled anyways :).

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.

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.

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...

Probably. But it also potentially locks more database objects at a time (depending on your database and your schema) and thereby decreases concurrency.In my experience people are often mislead by the "fewer database round-trips" argument when in reality on most OLTP systems where the database is on the same LAN, the real bottleneck is rarely the network.

For inner joins, a single query makes sense, since you only get matching rows. For left joins, multiple queries is much better... look at the following benchmark I did: Single query with 5 Joins query: 8.074508 seconds result size: 2268000 5 queries in a row combined query time: 0.00262 seconds result size: 165 (6 + 50 + 7 + 12 + 90) . Note that we get the same results in both cases (6 x 50 x 7 x 12 x 90 = 2268000) left joins use exponentially more memory with redundant data.

The memory limit might not be as bad if you only do a join of two tables, but generally three or more and it becomes worth different queries. As a side note, my MySQL server is right beside my application server... so connection time is negligible. If your connection time is in the seconds, then maybe there is a benefit Frank.

If we toss aside the annoying little fact that nobody in their right mind does a cross join between 5 tables (for that very reason, along with that in most cases it just doesn't make sense), your "benchmark" might have some merit. But left or inner joins are the norm, usually by key (making retrieval much faster), and the duplication of data is usually much, much less than you're making it out to be. – cHao May 3 at 22:25.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.