The purpose of the index is to pull the query results quickly from a Table. But, too many indexes on a Table slows-up performance. Due to its trickiness, this question has become a love for interviewers. I have shared an answer here that the number of indexes to create.
There is no limitation on the indexes need on a Table. However, database administrators do not create more indexes. The reason is it downplays performance. In other words, it will take more time to execute a query.
Why more Indexes are bad.
To be precise, 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.
The Rule of Thumb 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.