Close – you most likely want the following:
add_index :person_products, [:person_id, :product_id], :unique => true
add_index :person_products, :product_id
The :unique => true is not strictly required and it depends whether or not it makes sense to have a person associated with a product multiple times. I would say if you’re not sure, you probably do want the :unique flag.
The reason for the index structure is that all modern databases can execute queries on both person_id and product_id using the first index regardless of the order specified in a query. E.g.
SELECT foo FROM bar WHERE person_id = 1 AND product_id = 2
SELECT foo FROM bar WHERE product_id = 2 AND person_id = 1
are treated as the same and the database is smart enough to use the first index.
Likewise, queries using only person_id can also be run using the first index. Multi-column b-tree indexes can use fewer columns than they have provided they are specified from the left of the original declaration.
For queries using only product_id, this cannot be executed against the first index (since that index is defined with person_id in the leftmost position). Hence you need a separate index to enable lookups on that field alone.
The multi-column b-tree index property also extends to indexes with higher numbers of columns. If you had an index on (person_id, product_id, favorite_color, shirt_size), you could use that index to run queries using person_id, (person_id, product_id), etc, so long as the order matches the definition.