ACC1x: Using Indexes in Microsoft Access
ID: 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"
- like "string*"
- between
- >= > < <=
- 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 QryOthr
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo
|