ACC1x: Using Indexes in Microsoft Access

• 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:

1. General Information (storage of Microsoft Access tables and 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:

1. 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.

2. 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"
   

       - like "string*"
   

       - between
   

       - >=  >  <  <=
   

3. 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?

   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.

 1. 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.

 2. 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.

 3. 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.

 4. 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.