Microsoft Access 2010 Bible

Chapter 24: Optimizing Access Applications

857

Entire volumes of text have been devoted to the subject of database theory. Teaching database the-

ory is certainly beyond the scope of this chapter (or even this book). However, you should be

familiar with many basics of good database design.

Creating efficient indexes

Indexes help Access find and sort records faster and more efficiently. To find data, Access looks up

the location of the data in the index and then retrieves the data from its location. You can create

indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distin-

guish between records in which the first field may have the same value. If they’re defined properly,

multiple-field indexes can be improve the performance of queries. This is because Microsoft’s

Rushmore query optimization (the technology that Jet uses to optimize the speed at which queries

execute) knows how to use multiple-field indexes.

Cross-Reference
For more on indexes, turn to Chapters 2 and 3.

Deciding which fields to index

People new to database development typically make two mistakes: First, not using indexes and,

second, using too many indexes (sometimes putting an index on every field in a table). Both of

these mistakes are serious. Sometimes a table with too many indexes may give slower performance

than a table with no indexes. Why? When a record is saved, Access must check every index in the

table, taking time and using a considerable amount of disk space. The time used is rarely noticed

with a few indexes, but a lot of indexes can require a huge amounts of time for updates.

In addition, indexes can slow some action queries (such as append queries) because the indexes

for updated fields need to be updated while performing the query’s operations. Figure 24.8 shows

the index property sheet for a sample tblContacts table.

FIGURE 24.8

Indexing common search fields like ZipCode, CustomerType, and TaxLocation

When you create a primary key for a table, the field (or fields) used to define the key is automati-

cally indexed. You can index any field unless the field’s data type is Memo or OLE Object. You

should consider indexing a field if any of the following rules apply: