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

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?

Answers


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.


Need Your Help

HTML:JS:BOOTSTRAP ; validate the multiselect dropdown

javascript html validation twitter-bootstrap

In my web application we have used the twitter-bootstrap theme. I have to do client side validation for one form in my application. The form includes text fields,checkboxs and multiselect dropdowns...

ASP.NET MVC3 Razor views - extremely slow editing in VS2010

performance visual-studio-2010 asp.net-mvc-3

I've got a relatively small project written in ASP.NET MVC3. After working a while, Visual Studio 2010 becomes very slow in Razor views (other file types work fine). With "slow" I mean "every keyst...