What indexes should be added for a polymorphic association in Ruby on Rails?

Question

I have a polymorphic association in a Ruby on Rails model. In the migration, I have:

create_table "offer_defs" do |t|
  t.integer "product_def_id"
  t.string  "product_def_type"
  ...
end

I would like to add an index for this association. I am hesitating between the following options:

add_index :offer_defs, [:product_def_id, :product_def_type]

or

add_index :offer_defs, :product_def_id
add_index :offer_defs, :product_def_type

or maybe both ?

What are the pros and cons?

Answer 1

I would go with the latter option. It'll help if you need to count polymorphic attachments of a specific type, which you might. If you wanted the multi column index, I'd at least swap the order of the two columns (putting type first), since that type field is much more likely to be a query param on this table than the id field.

note: I'm assuming you're using mysql here, and my advice should probably be ignored, or at least checked, if you're using a different db.