ACC: Compound Indexes Must Restrict First Indexed Field
ID: Q98793
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.
Before an index can be used, queries with restrictions on multiple-field
(compound) indexes must restrict at least the first field of the index. You
can, however, also use adjacent fields in the index (up to 10 fields). If
the restriction is placed on a field other than the first field, the query
optimizer scans the table rather than using the index. This is not always
desirable because table scans are slower than index searches for most
queries.
MORE INFORMATION
By default, when a compound index is created in Microsoft Access, no
individual indexes are assigned to the fields included in the compound
index. This behavior is by design.
For the query optimizer to use an index, you must use a comparison of
either the first field in the compound index or the first field and any
number of adjacent fields (up to 10) that make up the compound index. You
must query the indexed fields in the order they appear in the Indexes
window, beginning with the first indexed field and continuing with adjacent
fields.
NOTE: This principle also applies to using criteria with the Find
method in Visual Basic for Applications (or Access Basic in Microsoft
Access 2.0 or earlier).
For example, consider a table (T1) containing three fields: key_part1,
key_part2, and key_part3. If there is a composite index created on these
three fields and all fields are the primary key, then:
SELECT * FROM T1 WHERE key_part2 = <value>
does not use the index because the first field, key_part1, is not used.
SELECT * FROM T1 WHERE key_part1 = <value> AND key_part3 = <value>
also does not use the index, because, although key_part1 is referred to,
key_part1 and key_part3 are not adjacent fields.
However, each of the following three SQL statements do use the index
because they each include the first field, or the first field and one or
more adjacent fields of the composite index:
- SELECT * FROM T1 WHERE key_part1 = <value>
- SELECT * FROM T1 WHERE key_part1 = <value> AND key_part2 =
<value>
- SELECT * FROM T1 WHERE key_part1 = <value> AND key_part2 =
<value> AND key_part3 = <value>
The above fields are not prohibited from having indexes on them; individual
indexes can be built for each field, allowing comparisons on those fields
with index searches. Be aware, however, that indexes can take up as much
(or more) space than the data.
REFERENCES
For more information about creating indexes, search the Help Index for
"indexes, creating," or ask the Microsoft Access 97 Office Assistant.
For more information about optimizing queries with Rushmore technology,
search the Help Index for "Rushmore technology," or ask the Microsoft
Access 97 Office Assistant.
Additional query words:
index query performance multi
Keywords : kbusage TblPriky
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbinfo