Making ActiveRecord faster by NOT indexing

Usually one of the first things I read about on how to speed up ActiveRecord is to index my columns to speed up the lookup of items. "Of course!" But could indexing too much be harmful?

Essentially, if your column is an enum, then indexing it could actually cause MySQL to do more work. Why? Because the data set is so large, the MySQL ends up doing a full scan. So things like keeping track if something is active (1 or 0) then you can expect indexing to hurt.

So how does this effect ActiveRecord? Well, if you're keeping track of whether a user is active or not you would not want to index that column alone. Nor would you want to index a type column if you were using single table inheritance, again, because there isn't a lot of variance in the type.

So make sure that you index the right things, like IDs and leave the enum-like columns alone.


comments powered by Disqus