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.
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.