Using indexes with queries
Identify fields you can index to improve query performance.
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:
Fields being sorted, including multiple fields being used for sorting.
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.
Fields used in criteria. When you are specifying multiple fields for more complex criteria, make sure you set indexes for all the
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
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.
Optimizing Queries with Indexes - Quiz