Using Indexes with Access Queries

By now you should be familiar with the overall benefit that can be achieved by using indexes on certain fields in tables. In this lesson, we will look closely at which fields in a query can be indexed to give you the greatest performance advantage.
Use the following fields for performance gains:

1. Fields being sorted, including multiple fields being used for sorting.
2. Fields being used for joins--meaning both sides of joined tables. For example, in the previous lesson, I showed you a query that uses both the Projects and the Hours tables. They were joined on the field called ProjectID. To get the maximum performance, you would set an index on the ProjectID field in both tables. When creating tables, Access 2000 sets indexes on certain fields based on the data type of the field. It will also create an index if the field is being used as a lookup field or the tables are used in a relationship.
3. Fields used in criteria. When you are specifying multiple fields for more complex criteria, make sure you set indexes for all the fields.

Even when you are sorting, joining, or using criteria on more than one field, for example, sorting on LastName, then FirstName, stick to using individual indexes on the fields rather than compound indexes. Access gives better performance using individual fields for indexes. As you add indexes, you risk affecting performance during data entry. However, the effect is greater when importing large amounts of data rather than individual records.
In the next lesson, you will learn how to identify the three different types of joins available and how they can be used.

Click the Quiz link below to answer a few questions about working with query field properties and optimizing queries with indexes.
Optimizing Queries with Indexes - Quiz