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
It depends on which queries will be the most frequent.
You may end up with three indexes.
If you have ... WHERE products_categories.product_id = XXX, create an index on product_id.
Same for category_id
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.
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.
