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

Does the uniqueIdentifier stays the same if an user resets his apple device?

iphone objective-c sdk uniqueidentifier uidevice

If i restore or reset an iphone/ipod/ipad, does the uniqueIdentifier stays the same or does it change?

Run As JUnit not appearing in Eclipse - using JUnit4

java eclipse unit-testing junit junit4

I'm trying to write JUnit4 tests for my web app, and they had been working fine previously. However, now when I try to run the tests by right clicking the class file -> Run As -> JUnit Test I don't...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.