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:
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.