Monday, March 30, 2009

Rails Caching and MySQL GROUP_CONCAT

A recent caching solution that we came up with involved using the hash of the concatenated "children" ids of another resource to identify when the identical and previously stored children payload could be delivered instead of constructing the payload from scratch (which is kind of expensive). Our current solution uses action caching and a custom ActionController::Caching::Fragments::FileStore. In our custom FileStore we override the real_file_path(name) method to use this "children signature" as part of the name. The class looks something like this:

This seems to work well and the next step is likely to look at using the children_sig value for the ETag as well.

One interesting thing that came out of this investigation was that I wondered if I could produce the hash using straight SQL - should be much faster right? Turns out that with MySQL there is a built in aggregate function that concatenates a column and it is called GROUP_CONCAT. To generate that hash in the database with MySQL the following will work:

Pretty cool! I'm not sure which other database products support this as a built in aggregate function but it doesn't appear that SQL Server does.

I found the following recent blog posts talking about other uses for GROUP_CONCAT as well: