mysql - good pratice: table with more then one index ?

Question

Sorry all,

I do have this mini table with 3 columns but, I will list the values either by one or other column.

1) Is it ok to have two of those columns with indexes?

2) Should we have only one index per table?

3) At the limit, if we have a table with 100 columns for example, and we have 50 of them with indexes, is this ok?

Thanks, MEM

Answer 1

It's fine to have many indexes, even multiple indexes on one table, as long as you are using them.

Run EXPLAIN on your queries and check which indexes are being used. If there are indexes that are not being used by any queries then they aren't giving you any benefit and are just slowing down modifications to the table. These unused indexes should be removed.

You may also want to consider multiple-column indexes if you have not already done so.

Answer 2

There is no problem with having more than one index per table, and no, one index per table isn't really a guideline.

Having too many indexes is inefficient because

• It requires additional storage
• MySQL needs to determine which index to use for a particular query

Edit : As per Pablo and Mark, you need to understand how your data is being accessed in order for you to build effective indices. You can then refine and reduce the indexes.

Answer 3

To quickly answer your questions, I think:

1. Yes, it's OK to have two or even more columns with indexes
2. Not necessarily. You can have more than one index per table.
3. It might be OK, it might be not OK. It depends. The thing with indexes is that they take space (in DISK) and they make operations that modify your data (INSERT/UPDATE/DELETE) slower since for each and everyone of them, there will be a TABLE and INDEX update involved.

Your index creation should be driven by your queries. See what queries are you going to have on your system and create indexes accordingly.