外键和索引 [英] Foreign keys and indexes

问题描述

我有两个表格：产品和类别。每个类别都有许多产品，而一个产品可以属于多个类别。

产品

  product_id  -  int主自动增量
名称 - 唯一
等
  

类别

  category_id  -  int主自动增量
的名字 - 独特的
等
  

我有许多关系的第三个表。 / b>

products_categories

  product_id  - >外键：products.product_id 
 category_id  - >外键：category.category_id 
  

我的问题是：我应该创建索引 product_id 和 category_id 在 product_categories 中用于更快的选择查询，如果是，是什么索引？
$ b

谢谢

解决方案

这取决于哪些查询最为频繁。

您可能会得到三个索引。

1. 如果您有 ... WHERE products_categories.product_id = XXX ，在 product_id 上创建索引。

$ b $对于 category_id



2. 在 category_id 和 product_id 如果您有 ... WHERE products_categories.category_id = XXX AND products_categories.product_id = YYY

然而，（category_id，product_id）上的索引可能是冗余的，并且 （CATEGORY_ID）。另外，（product_id，category_id）上的索引可能与（product_id）上的索引是多余的。所以你最终可能会得到两个索引（而不是三个），涵盖了所有频繁的查询需求。

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.

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.

这篇关于外键和索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！