I was working on a rails app today, and got to wondering about the very many

SHOW FIELDS FROM

MySql queries I was seeing.

I had always thought about whether there was a way to get rid of those completely, or reducing the number of them by caching or something.
Well, today I actually did some digging and found this little gold nugget about “SHOW FIELDS” calls in Rails.

Apparently, Rails does cache MySql “SHOW FIELDS ” with one notable exception.

However, upon inspection it turns out that Rails DOES NOT cache ‘SHOW FIELDS’ queries for has_and_belongs_to_many associations. So every time a select or an insert is done via a Rails has_and_belongs_to_many association, a ‘SHOW FIELDS’ on the join table is executed.

So the dreaded HABTM association strikes again!
Those darned things are pure evil eh? 😀

Well the awesome blog writer figured out a patch to fix the problem.

And in his tests he reported this (see image below for reference) …

After rolling this change out we have seen a very noticeable improvement on performance. By using New Relic’s Compare With Yesterday feature (see chart below) we can see that our application response time dropped from an average of about 560 ms per request to 420 ms per request. This is roughly a 25% performance increase. The yellow line shows today, the blue line is yesterday. CPU and database load decreased by about 25% as well.

A 25% improvement in application response time!?!?!
Sign. me. up.

I followed the Rails ticket on this particular issue and couldn’t figure out whether the fix had been rolled into a Rails version yet. I asked the blog author and he says the patch has been included in Rails 2.3, so if you’re running that, this does not apply to you.

I’m about to apply this patch to a couple of applications, hopefully it helps you too.
Please post in the comments about your experience. Same? better? worse?