Bala Paranj wrote:> 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.
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 Ross
http://tzinfo.rubyforge.org/ -- DST-aware timezone library for Ruby