Secondary Indices

A primary key (aka, primary index) serves as the main identifier for the rows/records of a table. Its chief purpose is to ensure uniqueness of the rows/records. By using a primary key like SSN in an employee table, we can be confident that it is possible to differentiate the individual employees -- even if they have the same name, gender, DOB, etc.

Many database products also use the primary key as a way to physically store and/or sort the records in the table. For instance, if the SSN is used as the primary key of the Employee table, then it probably will be much faster to access the records in SSN order than in any other order.

Some Problems:

1. What if the users of the database want to rapidly look up employees by last name?
   For example, "Show me everyone named Smith."
    
2. What if they want to look up employees by last name AND first name?
   For example, "What is John Smith's telephone number?"
    
3. What if they want to look them up by job title?
   For example, "Show me all 'Sales Clerks'."
    
4. What if they want to look them up by job title AND name?
   For example, "Do we have any Sales Clerks named John Smith?"
    
5. What if they want to look them up by gender, foot size and mother's maiden name?
   For example, "How many male employees wear size 12C shoes and have a mother originally named Smith?" (OK, I admit that's a bit silly.)

Well, if the employee records are physically stored in SSN order, then accessing them in any other order is likely to be slow. If the table is very large, then accessing the records in a non-key order is likely to be very slow. That's when it is handy to have pre-built secondary indices. If the designer of the database has included four secondary indices for (1) last name, (2) last and first name, (3) job title and (4) job title and name, then these four special uses of the Employee table should be acceptably fast even though the records are physically stored in SSN order. (We would probably agree that it is hard to justify an index on gender, foot size and mother's name. Sure, we could create one, but when would we ever really need it?)

There is no such thing as a free lunch... not in life and certainly not in the world of databases. The database designer's job is not to simply create every conceivable secondary index. Secondary indices take up precious storage space (hard disk and ram). The indices also consume precious time to update whenever changes are made to the physical records of the table. The designer's job is to include the important secondary indices and discard the unimportant ones. Sadly, making these decisions about future importance is easier said than done.

Footnote

If the designer creates a full name index (i.e., last name, then first name), then it isn't really necessary to create a simple last name index, too.

More FAQs