Microsoft Access 2010 Bible

Part I: Access Building Blocks

80

The importance of indexes

Microsoft’s data indicates that more than half of all tables in Access databases contain no indexes.

This number doesn’t include the tables that are improperly indexed — it includes only those tables

that have no indexes at all. It appears that a lot of people don’t appreciate the importance of index-

ing the tables in an Access database.

On the CD-ROM
As a demonstration of the power and value of indexes, this book’s CD includes a database named
IndexTest.accdb. This database includes two identical tables containing approximately 355,000 random
words. One table is indexed on the Word field, and the other is not. A small form (shown in Figure 2.27) lets
you query either the indexed or unindexed table, and shows the number of milliseconds the search takes.

FIGURE 2.27

frmIndexTest provides a quick and easy way to verify the importance of indexes.

In a number of repeated tests, the indexed table consistently finds a word in less than 1 millisec-

ond, while the unindexed search takes between 400 and 800 milliseconds. Displaying the results

you see in Figure 2.27 takes almost no time at all and doesn’t contribute to the overall time

required to run the query. It goes without saying that the actual time required to run a query

depends very much on the computer’s hardware, but performance enhancements of 500 percent

and more are not at all uncommon when adding an index to a field.

Because an index means that Access maintains an internal sort order on the data contained in the

indexed field, you can see why performance is enhanced by an index. You should index virtually

every field that is frequently involved in queries or is frequently sorted on forms or reports.

Without an index, Access must search each and every record in the database looking for matches.

This process is called a table scan and is analogous to searching through each and every card in

Rolodex file to find all the people who work for a certain company. Until you reach the end of the

deck, you can’t be sure you’ve found every relevant card in the file.

As mentioned earlier in this chapter, a table’s primary key field is always indexed. This is because

the primary key is used to locate records in the table. Indexing the primary key makes it much eas-

ier for Access to find the required tables in either the current table or a foreign table related to the