Microsoft KB Archive/104828

ACC1x: Using Indexes in Microsoft Access

Q104828

The information in this article applies to:

Microsoft Access versions 1.0, 1.1

SUMMARY

This article discusses how and when Microsoft Access uses indexes. This information is provided to assist you in designing tables, indexes, and queries that can be executed quickly.

MORE INFORMATION

This article is divided into three parts, as follows:

General Information (storage of Microsoft Access tables and indexes)
Rules and Limitations (when indexes can be used)
Examples (example queries and their associated indexes)

GENERAL INFORMATION

Table Storage

Microsoft Access stores information in 2K pages. Each table is made up of a 2K header page and as many 2K data pages as are needed to store the data in the table. Records are placed in a table in the order in which they are entered.

Index Storage

Indexes are also stored in 2K pages. Each index consists of a header page and leaf pages. Leaf pages contain a key generated from the value of the field that is indexed and a pointer to the 2K page on which that record resides.

Table Statistics

Microsoft Access maintains information on the approximate number of records in each table and the number of data pages on which that table resides. This information is used to determine the most effective way to locate or retrieve data in a table.

Query Optimization

The process of determining the fastest method to access data is called query optimization. Records in a single table can be retrieved using a base table scan or an indexed search, as described below:

Base Table Scan:

A base table scan involves reading each record in the table and determining whether or not it matches the criteria.

The drawback to this method is that each 2K data page must be loaded into memory and then each record examined. However, when the query includes most of the data in the table, or at least one record each from a majority of the data pages, a base table scan can be faster than an indexed search.

Indexed Search:

When an index is used, Microsoft Access searches the index for the first occurrence of the specified data and then loads into memory the 2K data page that contains the record to which the index points.

The drawback to this method is that both index pages and data pages must be loaded and read. In addition, an entire 2K page must be loaded into memory for each record reference by the index. However, when the criteria in the query restricts the results to a relatively small group of records, an indexed search may be much faster than a base table scan.

RULES AND LIMITATIONS

Microsoft Access can use indexes to perform the following three tasks:

To order records in a query.

NOTE: Adding an index does not mean that your records are automatically sorted. The only way to ensure that the records in a query are sorted is to add an ORDER BY clause.

To select records that meet a specific criteria. That criteria must be in one of the following formats:

- = "value"

- IN (valuelist)

or the equivalent: = "value" OR = "value" OR = "value"

To join two tables.

Will an Index Always Be Used?

The process of query optimization in Microsoft Access includes determining the fastest way to retrieve records from each table in the query. Each method available to Microsoft Access is considered and assigned a cost. That cost is based on several factors, including the following:

How will the data be used?
How many records total are there in the table?
How large is the index?

To understand why one method might be preferable over another, consider the following scenario:

The user wishes to view all records in the Orders table, sorted by order date. There is an index on the Order Date field. There are approximately 50 records on each 2K page in the Orders table.

The total time to read each page and then to sort the records using a base table scan may actually be faster than using the index on the Order Date field. To use the index, Microsoft Access must load the first page of the index, load one 2K page for each record to which the index points, then move to the next page in the index.

However, Microsoft Access has the ability to display the first screen of data while completing the remainder of the query in the background. For this form, Microsoft Access would likely use the index to quickly retrieve the first 30 or so records, populate the form so that the user can start working, and then retrieve the remaining records as they are needed.

EXAMPLES

NOTE: Specific tables listed in the examples below can be found in the sample database NWIND.MDB.

Q. How can I speed up a query that joins two tables?

A. By indexing the fields that are being joined in each of the tables. This action allows Microsoft Access to quickly find all records in the second table that match each primary key in the first table. Microsoft Access then uses the statistics from each table to determine the order in which to access them. For example, if you are joining the Customers and Orders tables, verify that the fields Customers.Customer ID and Orders.Customer ID are indexed.

Q. Example: SELECT [orders].* FROM orders ORDER BY [order id], [customer id];

If there is a primary key on the Order ID field, would it help to have an index on the Customer ID field?

A. In this case, all records from the Orders table are being retrieved; thus, it would not be beneficial to use the Primary Key index. However, it would be helpful, if the query is used to browse data, to have a compound index on the Order ID and Customer ID fields.

Q. Assuming a compound primary key on the Order ID, Customer ID, Employee ID fields, would the following syntax be used in the queries below?

A. 1) SELECT [orders].* FROM orders ORDER BY [order id], [customer id], [employee ID];

If you are browsing, yes, because the goal is to fill the first screen quickly and an index allows you to do that. If you are reporting, no, because the goal is to complete the report quickly and sorting is generally faster than traversing an entire index.

2) SELECT [orders].* FROM orders ORDER BY [order id], [customer id];

The answer to #1 applies to this query also. Microsoft Access can use one of several keys of an index, as long as it is the first part of the key that is used.

3) SELECT [orders].* FROM orders ORDER BY [customer id], [employee ID];

No, the index would not be used because the first field in the index is not included.

Q. Assuming a primary key on the Order ID field and an index on the Customer ID and Order Date fields, would either be used to execute the following queries?

A. 1) SELECT [orders].* FROM orders ORDER BY [order id] WHERE [customer id] = "AAA"?

Assuming the restriction is fairly comprehensive, Microsoft Access would use the Customer ID/Order Date index to solve the restriction and then to sort. Using the index on the Order ID field would be wasteful. It is possible to traverse the entire Order ID index, looking at each row to evaluate the restriction; however, this method is extremely slow, unless many records meet the criteria (Customer ID = "AAA").

2) SELECT [orders].* FROM orders ORDER BY [order id] WHERE [customer id] = "AAA" And [Order Date] = "5/5/92".

The answer to #1 applies to this query also. Assuming you use the Customer ID/Order Date index, Microsoft Access includes the Order Date restriction in the index seek.

3) SELECT [orders].* FROM orders ORDER BY [order id] WHERE [customer id] = "AAA" OR [Order Date] = "5/5/92"

Microsoft Access cannot use the index to solve the Order Date restriction.

4) SELECT [orders].* FROM orders ORDER BY [order id] WHERE [customer id] = "AAA" and [Order Date] LIKE "5/5/*"

The answer to #1 and #2 applies to this example as well.

5) SELECT [orders].* FROM orders ORDER BY [order id] WHERE [customer id] = "AAA" Or [Order Date] like "5/5/*"

No, as in #3, the index cannot be used.

Example: WHERE col1 > value1 and col1 < value2

Microsoft Access can use an index on col1 to retrieve only those records that meet the specific criteria.

Example: WHERE col1 < value and col2 > value

Microsoft Access must decide between the index on col1 or the index on col2.

Keywords : kbusage
Issue type : kbinfo
Technology :