Describe 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:
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:
Increased disk space usage (not a concern today, though it was in the past).
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.