When the WHERE clause uses AND or OR clauses and indexes are available for both sides of the AND or OR, more than one index can be used. Multiple indexes are not used for queries using FIND. FIND statements can only use one index.The following examples describe how the compiler uses multiple indexes:
- WHERE clauses using AND,
- WHERE clauses using OR, and
- WHERE clauses using CONTAINS.
1. WHERE using AND
When the selection criteria includes the use of AND, more than one index will be used when all the components of each index are involved in equality matches, and the indexes are not unique.
Example 1: Index Used: Name, SalesRep
Two indexes are used, because both indexes are non-unique and all components are involved in equality matches.
FOR EACH customer WHERE name = "Peter" AND salesrep = "Jim":
Example 2: Index Used: Name
Only one index is used, because the match is not an equality match.
FOR EACH customer WHERE name > "Martin" AND salesrep > "Jim":
Example 3: Index Used: CountryPost, SalesRep
Two indexes are used, because both indexes are non-unique and all components are involved in equality matches.
FOR EACH customer WHERE country = "USA" AND postalcode = "21000" AND salesrep = "Jim":
Example 4: Index Used: SalesRep
Only one index is used, since not all the components of CountryPost are involved in the equality match.
FOR EACH customer WHERE country = "USA" AND salesrep = "Jim":
Example 5: Index Used: Custnum
Only one index is used, because Custnum is unique.
FOR EACH customer WHERE custnum = 65 AND salesrep = "Jim":
2. WHERE using ORWhen an OR is used in the WHERE clause and both the left and right side of the OR contain at least the lead component of an index using either the equality or range matches, then multiple indexes are used.
Example 1: Index Used: CountryPost, SalesRep
Two indexes are used, since the selection criteria on both sides of OR use at least the leading components of both indexes.
FOR EACH customer WHERE (country = "USA" AND postalcode = "21000") OR (salesrep = "Jim"):
Example 2: Index Used: CountryPost, SalesRep
Two indexes are used, since the selection criteria on both sides of OR use at least the leading components o.f both indexes.
FOR EACH customer WHERE (country = "USA" AND postalcode = "21000") OR (salesrep > "Jim"):
Example 3: Index Used: CountryPost, SalesRepTwo indexes are used, since the selection criteria on both sides of OR use at least the leading components of both indexes.
FOR EACH customer WHERE (country = "USA") OR (salesrep = "Jim"):
Example 4: Index Used: CustnumSince the postalcode is not the leading component, two indexes are not selected. In addition, since an OR is used, Progress selects the primary index in this case.
FOR EACH customer WHERE (postalcode = "21000") OR (salesrep =Jim"):
Example 5: Index Used: Name, CountrypostTwo indexes are used, since both the matches are on the leading components of the index.
FOR EACH customer WHERE (name begins "J") OR (country = "USA"):
Example 6: Index Used: Custnum, Custnum
One index and two brackets used
FOR EACH customer WHERE (custnum = 99) OR (custnum = 187):
Example 7: Index Used: Custnum, Name, NameTwo indexes and three brackets used.
FOR EACH customer WHERE (custnum < 99) OR (name = "John") OR (name = "Scott"):
3. WHERE using CONTAINSWhen a CONTAINS clause with a column that has a word index is used in the WHERE clause, the word index, as well as other indexes, will be used.
Example 1: Index Used: Comments, Salesrep
Comments is word indexed, therefore it is selected.
Also AND is used with the equality match, therefore salesrep is also selected.
FOR EACH customer WHERE (comments CONTAINS "amount") AND (salesrep = "Jim"):
Example 2: Index Used: Comments, Name, CountryPost
Comments index is word indexed, therefore it is selected.
The AND after the comments contains an equality match, therefore it's selected and the selection on the right of OR uses the lead components of the index, therefore it is selected.
FOR EACH customer WHERE (comments CONTAINS "amount" AND name = "John") OR (country = "USA" AND postalcode = "21000"):
