Here are ideas to decide index quantity on a Table. Those are, how many indexes you need, and way to to create additional index on the existing Table.
There is no limitation indexes need on a Table. However, database administrators don’t create more indexes. The reason is it downplays performance. In other words, it will take more time to execute a query.
Indexes in the database store separately, so you can create or drop indexes anytime on a Table. That means even on existing Table you can create.
3 Rules to Create Indexes
- The optimum number of indexes is 3 or 4. But, this again depends on your shop.
- The primary key is Unique-Index by default. Checkout here Designing indexes.
- The Primary Key is different from the Index. The purpose of it is to maintain the relationships between tables.
Do We Need More Indexes?
- No, more indexes reduce performance.
- Indexes occupy physical disk space – how Tables do. So more indexes mean there is disk space overhead.
- Suppose you have created N indexes on a table. In tune with, the database tries to keep in sync these indexes for INSERT, DELETE, and UPDATE operations. Because of this, query performance will affect. This is the reason more indexes are bad.