Mysql query opinions

[The Little Guy]

I looking for your opinion on this:

 

I am trying to decide if it is a better idea to do one query using many joins or many queries a few joins, which way do you feel is easier to manage and/or faster to do?

 

I have been told that sometimes it is faster to do multiple small queries than it would be to do one larger join. What is your opinion on this?

 

here is an example that made me think of this, the first query returns everything needed, but it also returns a little extra which then requires extra php logic to format on the page. This particular query returns the game developers and publishers, so if there is 1 developer and 2 publishers, you will get 2 different publishers and 2 of the same developers.

 

select d.developer, d.developer_id, p.publisher, p.publisher_id, g.game_id from game_info gi
left join games g using(game_id)
left join game_publishers gp on(g.game_id = gp.game_id)
left join game_developers gd on(g.game_id = gd.game_id)
left join publishers p on(gp.publisher_id = p.publisher_id)
left join developers d on(gd.developer_id = d.developer_id) where gi.game_id = 1007

 

Each one of these queries returns exactly what is needed, so now all you need to do is loop through each result set.

 

select d.developer, d.developer_id from game_developers gd left join developers d using(developer_id) where gd.game_id = 1007

select p.publisher, p.publisher_id from game_publishers gp left join publishers p using(publisher_id) where gp.game_id = 1007

 

So, if you forgot the main question, what do you feel is a better method, many small queries or one large query (especially when using 1 to many and 1 to 1 in the same query)?

[Philip]

Personally, I'd probably end up doing the join... but I'd recommend benchmarking them (using mysqlslap or the like) to see which is better for you (server-wise)

[The Little Guy]

After doing those stress test, my data says the two small queries ran 2 times faster then the one larger query.

 

Results:

http://cleep.gmserver.net/stress/