There are many similar questions in SO such as "Multiple queries versus a single query."
But I did not see any general conclusion, so I am still confused by this.
So, I will ask about it in other terms:
When is it better to run multiple queries instead of a single query with multiple joins?
I am not asking for trivial cases, it is obvious that joining two or three tables is much faster than executing 3 queries.
I think in cases, for example, when you have 10+ unions, and some of these unions from many to many relationships, so your final request has GROUP_CONCAT, a combination of LEFT and INNER connections, etc.
For example, you want the name of the product, as well as all its images, as well as all their tags, as well as all their videos, as well as all directions where you can buy it.
Is it better to make a very long request with complex joins and group_concat (which is many times very difficult to handle if you cannot use a separate one) or execute a request for product details, a request for images, another for tags, etc.?
I can write a specific example if this helps clarify the issue. But I was hoping for a general rule for such situations.
Where is the limit? when is a single query with Joins worse than multiple queries?
, SELECT:
(autocommit = false)?
?
!