The AWDwR book has the following:
add_index :categories_products, [:product_id, :category_id]
add_index :categories_products, :category_id
The first, composite index actually serves two purposes: it creates an
index which can be searched
on both foreign key columns, and with most databases it also creates an
index that enables fast lookup by the product id. The second index then
completes the picture, allowing fast lookup on category id.
My question is if the composite index creates an index for both foreign
key columns then why do we
need the second add index for category_id? TIA.
Bala P. wrote:
My question is if the composite index creates an index for both foreign key columns then why do we
need the second add index for category_id? TIA.
You can consider the composite index to be an index on the concatenation
of the columns. Your composite index will be ordered first by product_id
and then by category_id.
Databases will only be able to use an index if you are searching a
leftmost prefix of its columns. Your composite index would be used for
the following two queries:
SELECT * FROM categories_products WHERE product_id=1 AND category_id=2
SELECT * FROM categories_products WHERE product_id=1
It won’t be used for the following query, since category_id does not
appear as the first column of the index:
SELECT * FROM categories_products WHERE category_id=1
The second index is needed to allow lookups by category_id.
–
Philip R.
http://tzinfo.rubyforge.org/ – DST-aware timezone library for Ruby