Are JOIN queries faster than several queries? (You take your primary query, and you run a number of other Chooses in line with the is a result of your primary query)
I am asking because JOINing them would complicate A Great Deal the appearance of my application
If they're faster, can anybody approximate very roughly because when much? Whether it's 1.5x I do not care, but when it's 10 x I suppose I actually do.
This really is far too vague to provide you with a solution highly relevant to your particular situation. It is dependent on several things. Shaun Atwood (founding father of this website) really wrote relating to this. Typically, though, if you possess the right indexes and also you correctly do your JOINs it is almost always likely to be faster to complete 1 trip than several.
I really found this searching for a solution myself, and after reading through the given solutions I'm able to only agree that the easiest method to compare DB queries performance would be to come on-world amounts because you will find simply to many variables to be taken into consideration BUT, I additionally believe that evaluating the amounts together results in not good the. I mean , the amounts ought to always be in comparison by having an acceptable number and certainly not in comparison with one another.
I'm able to understand if just one way of querying takes say .02 seconds and also the other one takes 20 seconds, that's a massive difference. But let's say just one way of querying takes .0000000002 seconds, and also the other one takes .0000002 seconds ? In the two cases one of the ways is really a whopping 1000 occasions faster compared to other one, but exactly how "whopping" could it be really within the second situation ?
Main point here when i personally view it: whether it works well, choose the simple solution. As lengthy as you've a team manager, your code can get trolled anyways :)
With respect to the complexity for that database in comparison to developer complexity, it might be better to do many Choose calls.
Try running some database statistics against both JOIN and also the multiple Chooses. Find out if inside your atmosphere the JOIN is faster/reduced compared to Choose.
On the other hand, if altering it to some JOIN means an additional day/week/month of dev work, I'd stay with multiple Chooses
Construct both separate queries and joins, then time all of them -- nothing helps a lot more than real-world amounts.
Then better still -- add "EXPLAIN" to the start of each query. This will explain the number of subqueries MySQL is applying to reply to your request data, and just how many rows scanned for every query.
Yes, one query using JOINS could be faster. Although not understanding the associations from the tables you're querying, how big your dataset, or in which the primary secrets are, it's nearly impossible to express just how much faster.
Why don't you test both situations out, then you will know without a doubt...
Could it be faster when it comes to throughput? Most likely. It potentially locks more database objects at any given time (based on your database as well as your schema) and therefore decreases concurrency. In my opinion individuals are frequently mislead through the "less database round-outings" argument much more reality of all OLTP systems in which the database is on a single LAN, the actual bottleneck isn't the network.
For inner joins, just one query is sensible, because you only get matching rows. For left joins, multiple queries is way better... consider the following benchmark Used to do:
Single query with 5 Joins
query: 8.074508 seconds
result size: 2268000
5 queries consecutively
combined query time: .00262 seconds
result size: 165 (6 + 50 + 7 + 12 + 90)
Observe that we obtain exactly the same results in the two cases (6 x 50 x 7 x 12 x 90 = 2268000)
left joins use tremendously more memory with redundant data.
The memory limit is probably not badly should you only perform a join of two tables, but generally three or even more also it becomes worth different queries.
Like a side note, my MySQL server is appropriate beside my application server... so connection time is minimal. In case your connection time is incorporated in the seconds, maybe there's an advantage