How Many Indexes Can be Created on a Table

Here are ideas on Indexes creation. One is how many indexes you need for a Table, and the second one is to create indexes 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

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

Indexes Vs Performance

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.

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.