This article was previously published under Q209564
This article has been archived. It is offered "as is" and will no longer be updated.
This article applies only to a Microsoft Access database (.mdb).
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. Youcan, however, also use adjacent fields in the index (up to 10 fields). Ifthe restriction is placed on a field other than the first field, the queryoptimizer scans the table, rather than using the index. This is not alwaysdesirable, because table scans are slower than index searches for mostqueries.
By default, when a compound index is created in Microsoft Access, noindividual indexes are assigned to the fields included in the compoundindex. This behavior is by design.
For the query optimizer to use an index, you must use a comparison ofeither the first field in the compound index or the first field and anynumber of adjacent fields (up to 10) that make up the compound index. Youmust query the indexed fields in the order that they appear in the Indexes dialog box, 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.
For example, consider a table (T1) that has three fields: key_part1,key_part2, and key_part3. If there is a composite index created on thesethree fields and all fields are the primary key, then the following SQL statement does not use the index, because the first field, key_part1, is not being used.
SELECT * FROM T1 WHERE key_part2 = <value>
Neither does the following SQL Statement make use of the index. Although key_part1 is referred to, key_part1 and key_part3 are not adjacent fields.
SELECT * FROM T1 WHERE key_part1 = <value> AND key_part3 = <value>
However, each of the following three SQL statements will use the indexbecause they each include the first field, or the first field and one ormore 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 individual 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.
For more information about creating indexes, click Microsoft Access Help on the Help menu, type creating an index in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
For more information about optimizing queries, click Microsoft Access Help on the Help menu, type optimize performance in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.