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?

Thanks

PS: please dont take into account SQL syntax, I haven't checked it out against a real database, it is only design question, thanks

Answers


I would only consider two options:

  1. One query:

    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
    
  2. Two queries:

    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.


Need Your Help

Kinvey Rest API — Aggregation (Map/reduce) using C# back end

c# api rest mapreduce

I was going through kinvey (BAAS) documentation and I came across Aggregation concept using Map/reduce.

C++ in eclipse: After an update?: CDT errors

c++ eclipse

Since yesterday my eclipse does not work anymore for c++ projects ( After a restart of the computer because of general updates). I work with ubuntu. Re-installation and with a new installation of

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.