In the SQL query, the index makes search faster. This pulls the query results quickly. But, too many indexes are bad. This question asked in interviews. So I am sharing an answer in this post.
There is no limitation on the indexes. Still, database administrators do not create more indexes. The reason is it downplays query performance. In other words, it slows query execution.
Why more Indexes are bad.
To be precise, Indexes occupy physical disk space like Tables. Keep in mind that more indexes mean there is disk space overhead.
If you create N indexes on a table, the database keeps in sync these indexes for INSERT, DELETE, and UPDATE operations. So this downplays your query performance.
Good Rule of Thumb
Always, use primary key and unique indexes in SQL WHERE clause.
I am just telling you that the Primary key is the Unique index by default. This also maintains a relationship with the foreign key of another table.
Always keep in mind that the Primary Key is different from the Index. The use of the Index is to fetch the data quickly. And, Primary Key maintains the relation between tables.