CRAZ8

I tracked down a performance issue in my code today that was pretty subtle.

I have a table with a varchar column that contains a number, mainly for size reasons. This column has an index all to itself, so queries using the column should be pretty snappy, but they weren’t.

My code went something like this:

  find(:all, :conditions => "my_col = #{value}")

I know I shouldn’t have built the conditions that way, but that’s the way the code was. And the performance sucked!

Since my_col is actually a string, I need quotes around the value otherwise MySQL has to try to figure it out, and it misses the index completely!

So, in future, use the :conditions the correct way, as Active Record does a pretty good job of getting the quotes right!

So, why did I write the code this way?

The code I actually wrote is for the parameters to a call to update_all, and the documentation for update_all isn’t as clear as it could be on how the parameters work. The key is that both of the first 2 parameters to update_all are the same as a :conditions parameter to find (but without the :conditions symbol). So they can be a string, or an array that has Active Record parameter substitution.