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

Question

I have a polymorphic association in a Ruby on Rails model. In 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 hesitate 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 last option. This will help if you need to count the polymorphic attachments of a certain type that you can. If you need an index of several columns, I would at least change the order of two columns (the first input type), since this type field is much more likely to be a query parameter in this table than the id field.

note: I assume that you are using mysql here, and my advice should probably be ignored, or at least checked if you are using a different db.