Indexes can be used with the following types of selection criteria:
| Selection Criteria | Example |
|---|---|
| Simple selection criteria | Indexes are utilized with simple selection criteria statements.
For example: select filename with attr "value" Indexes are used when multiple values are specified, as long as they are part of the same selection clause. For example: select filename with attr "value1""value2" |
| Not equal to (#) boolean operator | Indexes are utilized in statements containing the not equal
to (#) boolean operator. For example: select filename with attr # "value"Indexes are not used when the selection criteria contains multiple boolean # operators. |
| Wildcards | Indexes are utilized in statements containing wildcards. For
example: select filename with attr "value]" Indexes are not used when multiple wildcards are specified, unless each wildcard is in its own and selection clause. For example: select filename with attr1 "value]" and with attr2 "value]" Note: The ^ wildcard cannot be used in
select statements against an indexed attribute. Doing so can cause
undefined results.
|
| and operator | Indexes can be used when multiple and operators
are specified, so long as one of the attribute definitions in the
selection criteria contains an index. For example: select filename with attr1 "value1" and with attr2 "value2" and with attr3 "value3"If either attr1, attr2, or attr3 contains an index, indexes are utilized. |
| Logical operators | Indexes are used in statements containing logical operators, so long as all of the above requirements are also met. |
Certain caveats apply to the use of indexes in Access:
Indexes are not utilized if an or clause is present in the statement.
Indexes are not utilized if an Access statement is executed while a select list is active.
Rules for attribute 0 are the same as other attributes, as long as the A0 selection criteria is presented in the form:
select filename with a0 "value"
Indexes are not utilized if this form is presented:
select filename= "value"
As of D3 9.0, all comparisons except those using the equals operator (eq or =), use the index. When multiple indexes are available for use in your selection criteria, indexes are processed in the order in which they appear in the query. As such, to decrease query processing time it is highly recommended that queries are constructed with an index ordering that starts with the index that returns the fewest selections. For example, assuming MYFILE has an index for both DATE and TYPE:
SELECT MYFILE IF DATE = "3 MAY 2012" AND IF TYPE NE"REFUND"
will run much faster than:
SELECT MYFILE IF TYPE NE "REFUND" AND IF DATE = "3 MAY 2012"
Since few items are type REFUND, and the comparison uses the NE operator, the second query will return a very long list of Item-IDs to process. Conversely, far fewer items would have been processed on a particular date. Therefore, placing the DATE selection first would make use of the DATE index, generating a very small list of Item-IDs to process.