how many index per database table?

Question

Hi, How many indexes is preferred for one table in MYSQL??

I have a company table where I search mostly for ID,state and category?

Thansk

Answer 1

There is no "preferred" number of indexes, it all depends on your queries. In your example, you would need 3 indexes, one for ID, one for state, and one for category.

Answer 2

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")

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.