0

I've seen few times the following migration in Rails:

create_join_table :customers, :products do |t|
  # t.index [:customer_id, :product_id]
  # t.index [:product_id, :customer_id]
end

I don't understand why we need to declare the same index twice, or the order of columns in index has a great priority? Does it increase speed of queries? Thanks in advance.

malcoauri
  • 11,904
  • 28
  • 82
  • 137

1 Answers1

0

I think the best way to explain indexes is with the phone book example. The phone book is organized by Last Name, First Name. You can easily look someone up by their last name or their last name AND first name, BUT you can't easily look someone up just through their first name. To easily search through by first name only you would need a separate index.

So coming back to your example, they have an index on customer_id and product_id so you can easily find a customer_id or a customer_id and a product_id. But the first index alone doesn't allow you to search easily by product_id. So the second index allows you to easily find a product_id or a product_id and a customer_id.

If you have a has_many :through relationship, your join table should have a unique index on both properties involved in the join as a compound key which is what you have presented.

This question and answer is a pretty good resource for some more information on when to use indexes in rails: When to add what indexes in a table in Rails

Community
  • 1
  • 1
ChrisBarthol
  • 4,871
  • 2
  • 21
  • 24
  • So, if I use this 2 indexes, I don't need to create indexes for product_id and customer_id separately because it's already done with index of 2 columns? – malcoauri Sep 02 '14 at 12:46
  • And, when I need to get record by product_id and customer_id I can do it faster using the first index from my example or the second index. Why do I need it both? – malcoauri Sep 02 '14 at 12:47
  • You don't need to create a seperate index just for product_id as the second index is already indexing that. Doing both provides the option for speed. Some queries may run faster by looking up product_id before customer_id and vice verses. With a multikey index you want the more unique value to be first to reduce the number of records. So it really depends on the queries you are running and your data. – ChrisBarthol Sep 02 '14 at 14:47
  • This article may help as well: http://blog.evanweaver.com/2007/02/12/table-indexes-in-rails/ – ChrisBarthol Sep 02 '14 at 14:48