SQL Table Indexes: How to Decide Optimum Number

Deciding how many indexes to create on a table is crucial for database performance. Here are some guidelines to help you determine the number of indexes you should have.

Creating too many indexes in a SQL table can slow down the queries. Indexes are stored separately and can be added or removed anytime, even from existing tables. So, database administrators are careful not to create excessive indexes to maintain good performance.

How to decide the number of indexes on a table

The optimum number of indexes is 3 or 4. But, this again depends on your shop.
The primary key is a Unique-Index by default. Check out Designing indexes here.
The Primary Key is different from the Index. Its purpose is to maintain the relationships between tables.

Disadvantages of Adding More Indexes on a Table

Indexes can impact performance negatively.
They use disk space, just like tables.
If you have multiple indexes on a table, the database keeps them in sync for insert, delete, and update operations. It can slow down query performance. So, having too many indexes can be detrimental.