RelationalDBDesign RelationalDBDesign



Physical Design   «Prev  Next»
Lesson 3Indexing
ObjectiveExplain the purpose of creating Indexes

Create an Index using SQL

Whenever you add rows to a table the RDBMS writes them to the end of the table, regardless of the values in the table. For example, a list of orders Stories on CD placed with its distributors would go in chronological order, with no concern for the titles or the values for CDNo.
Order table consisting of 1) OrderNo 2) OrderDate 3) CDNo 4) OrderCost 5) Received

Because the records are not ordered according to CDNo, the RDBMS would need to search every record of this table to find all orders for Northern Tales (CDNo 101). If users frequently needed to search the Orders table to find every order for a particular CD, the amount of time the RDBMS took to search the entire table (which could be quite large) would impact worker productivity.
Rather than force the RDBMS to examine every record, it is possible to create an index of values in fields frequently used in searches and joins. An index is an ordered list of values in a field, exactly like the index in the back of a book.

An index consisting of 1) CDNo 2) OrderNo



Rather than search the table, the RDBMS can search the index and, when it finds the value it wants, use the already recorded positions to quickly pull the data from the table.
The next lesson describes the trade-offs of indexing.

Deciding Which Indexes to Create

You have no choice as to whether the DBMS creates indexes for your primary keys; you get them whether you want them or not. In addition, you can create indexes on any column or combination of columns you want. However, before you jump headfirst into creating indexes on every column in every table, you must consider some trade-offs:
  1. Indexes take up space in the database. Given that disk space is relatively inexpensive today, this is usually not a major drawback.
  2. When you insert, modify, or delete data in indexed columns, the DBMS must update the index as well as the base table. This may slow down data modification operations, especially if the tables have a lot of rows.
  3. Indexes definitely speed up access to data.