Working with ActiveRecord has more or less trained me, after the dust of new application development settles, to go through my logs and EXPLAIN the generated SQL queries. My response is usually dread, confusion, disappointment, and then some ideas for better table indexes, usually in that order.

Viewing the logs for queries utilizing acts-as-taggable-on and stacked tag scope recently, I noticed that joins against the Tag table from the Tagging table on tags.id and tags.name weren’t using indexes. Of course, the Tag table migration looks like this, so no surprise there.

 create_table :tags do |t|
   t.column :name, :string
 end
# no indexes…

But why isn’t name indexed? For my queries I was seeing a lot of “WHERE tag.name = ‘blah’ OR tag.name = ‘meh’ OR. . .” etc. This is the part of the query that shows up in EXPLAIN results as unindexed. Adding the following, however, seemed to satisfy the MySQL query engine, though I was taking action as a matter of best practice and don’t actually have profiled data to prove that any real-world impact came about from the addition.

create_table :tags do |t|
   t.column :name, :string
 end
add_index :tags, [:id, :name]