Chapter 14, Problem 1PE

Explanation of Solution

Indices and its purpose:

• Indices provide link to data with a specific value.
• Indices also used to make the search faster.
• Indices should not be created on every searchable attribute because of the following reasons:
  • Firstly, an index takes extra storage space in memory.
  • Index also includes additional overhead in terms of both CPU time and disk I/O at the time of insertion and deletion of records.
  • When it comes to updates, there is no need to update the index if it is not present on a primary key, but if the index is present on a non-primary key, it has to be changed with every update.
  • If the queries contain conditions for many search keys, use the indices on few of the keys which is sufficient to offer better efficiency.
  • Lastly, if there are too many indices in the database, the improvement performance is not much.