Fetch row and its foreign key rows in one query vs many queries
let's say I have three tables (MySQL), one for users, one for tags and a last one for joining them (many to many relation):
create table user_tag( user_id int unsigned not null, tag_id int unsigned not null, primary_key (user_id, tag_id) );
I want to get out of the database a complete list of users (or at least many of them) along with the tags they have associated. I use PHP as a server language for that.
So my question is, is it better to perform one SQL query fetching all the information like this:
select user.name, user.image, ..., tag.name from user, tag, user_tag where user.user_id = user_tag.user_id and tag.tag_id = user_tag.tag_id;
or is it better to perform one first query for the users and afterwards fetch their tags:
select user.name, user.image, ... from user;
and then for each user:
select tag.name from tag, user_tag where tag.tag_id = user_tag.tag_id and user_tag.user_id = $USERID;
I feel like second is better option but I am afraid that can be too much queries for the database (NOTE that this is a general design example, but this pattern can be present multiple times over the database with different tables).
Which is better? Pros and Cons? Other ways?
PS: please dont take into account SQL syntax, I haven't checked it out against a real database, it is only design question, thanks
I would only consider two options:
SELECT user.user_id, user.name, user.image, ..., tag.tag_id, tag.name FROM user LEFT JOIN user_tag ON user.user_id = user_tag.user_id LEFT JOIN tag ON user_tag.tag_id=tag.tag_id
SELECT user_id, name, image, ... FROM user SELECT user_tag.user_id, tag.tag_id, tag.name FROM user_tag INNER JOIN tag ON user_tag.tag_id=tag.tag_id
If you have 100 users, there's no point in issuing an identical query 100 times.
I normally go for #1 when I'm only fetching a few columns and I go for #2 otherwise. In this case, you seem to be retrieving a pretty complete user profile so #2 sounds good.