MySql Lovin' Part 3: Summing up with group_concat

Ruby on Rails provides some handy methods for using SQL aggregate functions:

• count
• average
• minimum
• maximum
• sum

While this is a good start, MySql has additional functions that are not included in this list. I will show an example using group_concat.

group_concat

If you recall from my post about on duplicate key update, we had a Post model that included a virtual attribute called tag_list:


This code retrieves all tags for the post, creates Tag objects, maps that to an array of strings, and joins them together. While this code looks nifty, it is performing a hefty amount of work for the task.

Thankfully, MySql has a group_concat function, and Rails exposes a calculate method to perform arbitrary aggregate functions by name. Without further ado, here is how we can use group_concat:


The separator defaults to ','. If you want to change it, sneak in a separator command:


group_concat will be significantly faster than the original method.

Another group of underused aggregate functions are bit_and, bit_or, and bit_xor. If you have a many-many association between a model and an enumerated set of 64 or less items, this information can be stored in a single BIGINT field on the model.

This wraps up my unglamorous MySql series. If I saved one clock cycle on one database server, then I claim success.