MySQL Query Optimization (with COUNT and JOINs)

Question

I have a query that grabs information from four different tables, and joins them all. The tables are 'guilds', 'guilds_memberships', 'flats', and 'users'. The query I have works, but i'm just wondering about the efficiency of it and if it can be made faster.

I mean at the moment it works fine and is speedy, but there isn't much data inside of guilds_memberships, and it's being COUNTED()'d. The query i'm using is shown below. (there will always be at least 1 record for each guild inside of guilds_memberships, flats, and users btw, if that'll help with optimization at all (no reason to check for NULLs))

SELECT g.id, g.roomid, g.ownerid, g.roomid, g.state, g.name, g.description, g.badgestring, g.primarycolour, g.secondarycolour, g.created, u.username, f.FlatName, COUNT( m.guildid ) AS Members
FROM guilds g
INNER JOIN users u ON u.id = g.ownerid
INNER JOIN flats f ON f.FlatID = g.roomid
INNER JOIN guilds_memberships m ON m.guildid = g.id
WHERE g.id =1
LIMIT 0 , 30

Sergio Tulentsev · Accepted Answer · 2011-12-27 16:43:51Z

1

Well, I don't think you can do something to greatly improve performance of this query. It looks ok, and even performs great.

But, if it starts to perform less great, here are steps that I would do:

Break this query in parts. Retrieve guild info in one query, user info in another, etc. Let application code do the joining.
Cache what's cacheable. I would bet that u.username and f.FlatName don't change too often, so you can load them once and keep in memory (or memcached).

Purpose of these changes is to lessen load on the database. App servers (usually) are trivial to scale, just fire up more workers. Database, on the other hand, is one of the hardest to scale components.

answered Dec 27, 2011 at 16:43

Sergio Tulentsev

231k43 gold badges381 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DominicEU Over a year ago

Thanks for your speedy answer. u.username would never change, guilds are not interchange-able and can only be deleted. Therefore in a guilds existence u.username is always the same, and though f.FlatName is changeable, you're right that it'd be changed infrequently. I'll bare in mind what you said about using memcache. Might prove useful in the future.

Collectives™ on Stack Overflow

MySQL Query Optimization (with COUNT and JOINs)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related