RelationalDBDesign RelationalDBDesign

Physical Design   «Prev  Next»
Lesson 4Downside to indexing
ObjectiveDescribe the trade-offs that occur by creating Indexes

Disadvantages of Field Indexing

Indexing a field allows an RDBMS to efficiently search for values in that field, though updating an index every time a new record is added to a table takes time. It is almost never correct to index every field in a table.
For instance, consider the following Order table:

CD table with columns 1)OrderNo 2) OrderDate 3) CDNo 4) OrderCost 5) Received

While it would definitely make sense to create an index for the CDNo and OrderNo fields, indexing the OrderCost field would be a waste of time unless you needed to find out how many items Stories on CD purchased at each price point.

Choosing which indexes to create

Indexing a field results in faster searches and joins. But it also involves the following trade-offs:
  1. Increased disk space usage (not a concern today, though it was in the past).
  2. Slower insertions, deletions, and updates (the index must be updated every time one of these operations occurs).

Every RDBMS will automatically index a table's primary key field(s). Beyond that, you will generally get the best results from indexes if you create an index for every foreign key field, though if you use a non-foreign key field in a lot of searches and joins, you should check to see how indexing the field affects overall performance.
As always, listen to your users: they'll tell you if the increase in data access speed is worth the decrease in update speed.
The next lesson introduces clustering.

Disadvantages of Indexing - Quiz

Before you move to the next lesson, click the Quiz link below to reinforce your understanding of indexing.
Disadvantages of Indexing - Quiz