One request with many joins in one API request or several requests with some joins in separate API requests?

Question

What is the best practice that provides the best performance?

I currently have a query with many LEFT JOIN that retrieves user and all of his data like friends, friend requests, etc:

 SELECT `user`.`id` AS `user_id`, `user`.`name` AS `user_name`, `manager`.`id` AS `manager_id`, `competition`.`id` AS `manager_competition_id`, `competition`.`name` AS `manager_competition_name`, `competition`.`week` AS `manager_competition_week`, `country`.`id` AS `manager_competition_country_id`, `country`.`name` AS `manager_competition_country_name`, `club_template`.`id` AS `manager_club_template_id`, `club_template`.`name` AS `manager_club_template_name`, `club`.`id` AS `manager_club_id`, `club`.`name` AS `manager_club_name`, `club`.`ready` AS `manager_club_ready`, `friend`.`friend_id` AS `friend_id`, `friend_user`.`name` AS `friend_name` FROM `users` AS `user` LEFT JOIN `managers` AS `manager` ON `manager`.`user_id` = `user`.`id` LEFT JOIN `competitions` AS `competition` ON `competition`.`id` = `manager`.`competition_id` LEFT JOIN `countries` AS `country` ON `country`.`id` = `competition`.`country_id` LEFT JOIN `club_templates` AS `club_template` ON `club_template`.`id` = `manager`.`club_template_id` LEFT JOIN `clubs` AS `club` ON `club`.`id` = `manager`.`club_id` LEFT JOIN `friends` AS `friend` ON `friend`.`user_id` = `user`.`id` LEFT JOIN `users` AS `friend_user` ON `friend_user`.`id` = `friend`.`friend_id` WHERE `user`.`id` = 1 

As you can see, this is a very big request. My reasoning was that it is better to have only one request that can be executed in one API request, for example ...

 /api/users/1 

... compared to multiple requests, each in its own API request, for example ...

 /api/users/1 /api/users/1/friends /api/users/1/friend_requests /api/users/1/managers 

But now I'm worried that since it has become such a huge request that it will actually hurt performance more than splitting it into separate API requests.

What will scale better?

Update

I changed the request to the full request. This is not a final request; I plan to add even more joins (or not, depends on the answer).

Each table has a PRIMARY KEY on id . All association columns ( competition_id , club_id , etc.) have a regular INDEX . The core of the database is InnoDB.

Answer 1

Of the two, I would recommend the latter: many niche queries. This gives the caller the flexibility to undo just what they want, and less likely to silently include performance issues (for example, only one option to retrieve data, so everyone uses it no matter how small the subset of that data they are really interested).

However, this is certainly not immune to performance issues, it simply means that the caller may be more aware of them by issuing so many API calls.

You could provide both options. It is clear from your naming agreement that the expensive version returns all the data and is intended for use when the user may need to make, for example, 20-30 calls to get the full image.

Examples:

1 - Imagine you need to get this full custom object in order to find out the name. Really wasteful. And if this is done unintentionally in a large loop, the waiting queue is waiting. They prefer the getUserName(id) method, which simply reads one value back.

2 - on the other hand, if you want to display the full user profile on the page, then the most effective getFullUserProfile(id) (1 call, not 10-20).

Edit is another useful example. Assume where many values ​​are searched, for example. instead of forcing the caller to run getUserName(id) 500 times to get all the names for a specific condition (maybe all admin users?), provide a List<String> getAdminUserNames() that provides all this data in one call.

Answer 2

Cold question.

I think you need to worry about the domain concepts underlying the request and try to stay as loyal as you can.

So, based on your request, you have users in different states of completion - users who have created their profile but have not yet joined the competition; users who have joined the contest but have not yet formed a club, etc. This reflects your domain model. I would expect your API to reflect this, so use your example:

 /api/users/profile /api/users/signedUpUsers/ /api/users/usersWithClubs/ 

The first call (/ api / users / profile) allows you to return the user profile, but none of your external union data depends on the state of the user (and possibly URLs where other additional data can be found).

Follow the domain approach and create a performance test in the development life cycle; optimize when you go, and only change the design if you can prove that you have a problem.