How many indexes is preferred for one table in MySQL??
在MySQL中，一个表需要多少索引?
I have a company table where I search mostly for ID, state, and category.
我有一个公司表，我主要搜索ID、状态和类别。
2 个解决方案
#1
5
There is no one single answer for this question: create as many indexes are required to make your queries fast enough, and no more.
对于这个问题，没有一个单一的答案:创建尽可能多的索引以使查询足够快，而且不会有更多的索引。
You have to trade off various things when deciding what to index:
在决定索引时，你必须权衡各种因素:
- The more indexes you have, the slower inserts and updates will be (because they need to update all of the indexes)
- 索引越多，插入和更新就越慢(因为它们需要更新所有的索引)
- Queries over tables without an appropriate index can be very slow, if the database has to do a table scan
- 如果数据库需要进行表扫描，那么对于没有适当索引的表的查询可能非常慢。
- Some columns do not require indexes (for example, a 'gender' column where the only possible value is "M" or "F")
- 有些列不需要索引(例如，“性别”列，其中惟一可能的值是“M”或“F”)
For your particular scenario, it seems like three indexes, one on ID (if this is the primary key, then it already has an implicit index), one on state and one on category, would suffice.
对于您的特定场景，似乎有三个索引，一个在ID上(如果这是主键，那么它已经有一个隐式索引)，一个在状态上，一个在类别上，就足够了。
#2
0
There is no "preferred" number of indexes, it all depends on your queries. In your example, you would need no more than 3 indexes, one for ID, one for state, and one for category.
没有“首选”索引数量，这完全取决于您的查询。在您的示例中，您将不需要超过3个索引，一个用于ID，一个用于状态，一个用于类别。
#1
5
There is no one single answer for this question: create as many indexes are required to make your queries fast enough, and no more.
对于这个问题，没有一个单一的答案:创建尽可能多的索引以使查询足够快，而且不会有更多的索引。
You have to trade off various things when deciding what to index:
在决定索引时，你必须权衡各种因素:
- The more indexes you have, the slower inserts and updates will be (because they need to update all of the indexes)
- 索引越多，插入和更新就越慢(因为它们需要更新所有的索引)
- Queries over tables without an appropriate index can be very slow, if the database has to do a table scan
- 如果数据库需要进行表扫描，那么对于没有适当索引的表的查询可能非常慢。
- Some columns do not require indexes (for example, a 'gender' column where the only possible value is "M" or "F")
- 有些列不需要索引(例如，“性别”列，其中惟一可能的值是“M”或“F”)
For your particular scenario, it seems like three indexes, one on ID (if this is the primary key, then it already has an implicit index), one on state and one on category, would suffice.
对于您的特定场景，似乎有三个索引，一个在ID上(如果这是主键，那么它已经有一个隐式索引)，一个在状态上，一个在类别上，就足够了。
#2
0
There is no "preferred" number of indexes, it all depends on your queries. In your example, you would need no more than 3 indexes, one for ID, one for state, and one for category.
没有“首选”索引数量，这完全取决于您的查询。在您的示例中，您将不需要超过3个索引，一个用于ID，一个用于状态，一个用于类别。