How to Design Indexes on a Table

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.

How to Design Right Number of Indexes on Table
Photo by Andrea Piacquadio on Pexels.com

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.

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.