Foreign keys and indexes

Question

I have 2 tables: products and categories. Each category has many products, and a product can belong to many categories.

products

product_id - int primary auto increment
name - unique
etc.

categories

category_id - int primary auto increment
name - unique
etc.

I have a 3rd table for manyTomany relations.

products_categories

product_id -> foreign key: products.product_id
category_id -> foreign key: category.category_id

My question is: should I create indexes for product_id and category_id in product_categories for faster select queries, and if yes, what indexes?

Thanks

Answer 1

It depends on which queries will be the most frequent.

You may end up with three indexes.

1. If you have ... WHERE products_categories.product_id = XXX, create an index on product_id.

2. Same for category_id

3. Create an index on both category_id and product_id if you have ... WHERE products_categories.category_id = XXX AND products_categories.product_id = YYY

However, following the MySQL manual, keep in mind that an index on (category_id, product_id) may be redundant with an index on (category_id). As well, an index on (product_id, category_id) may be redundant with an index on (product_id). So you may finally end up with two indexes (instead of three) covering all your frequent query needs.

Answer 2

You definitly should create indices, as the FOREIGN KEY constraint says nothing about the source field for the constraint, so it doesn't auto-create an index.

For the question "which index" you ofcourse do not want a unique index - and I'd stick with a default index which is BTREE.