Question

If a heavily updated table contains many indexes, the time required to rebuild those indexes may degrade the overall performance of the database.

Select one:

a. True

b. False

Answer 1

True

The update query on the table having multiple indexes will slow down the overall performance of the database.

Index: Index in the database table is used to speed up the select operation where the index column is used to search the required data and return the matching record in master table. Using index, instead of traversing each record one by one, only the index column is used to match the input criteria. An index can be based on a primary key column, on a candidate key OR it can be on a non-key attribute.

Example: A user table can have the below index:

(User_ID) : It will fetch the data based on the user id of the user by traversing ony the user id index. When the user_id matches with required record, it will give the other attribute’s values from the table as it hold a pointer to the all data for the searched user_id. If query needs U01 data, index will point to all related data for U01 and return the result faster.

Performance while Update on table with many index: When the table has multiple index, it will use multiple columns in indexes. The indexes are the copy of the index column in a separate column. Whenever the data of the table gets update, index column data will also get changed. It will require the change of index column as well at multiple indexes. The whole process will eventually slow down the database performance.

Example: Suppose the user table of above example has below indexes:

1. (User_ID)
2. (First_Name, User_ID)
3. (User_ID, Department)

If the User_Id is updated in the table, it will require the update in all above index columns, which will slower the database performance.