Having selected one or more indexes to satisfy a query, the AVM tries immediately to isolate the smallest necessary index subset, so as to return as few records as possible. This is called bracketing. Careful query design can increase the opportunities for bracketing, thereby preventing ABL from scanning entire indexes and examining all records. The rules for bracketing are simple:

  • Bracket on active equality matches.
  • Bracket an active range match, but no further brackets are possible for that index.

The following table provides some bracketing examples:

Sample WHERE clause Indexes used Brackets 
WHERE Contact = "DLC" 
 AND (Sales-Rep BEGINS "S"
   OR Sales-Rep BEGINS "B")
 Cust-Num None 
WHERE Postal-Code >= "01000" 
 AND City = "Boston"
 Cust-Num None 
WHERE Name = "Harrison" 
 AND Sales-Rep BEGINS "S"
Name Name 
WHERE Contact = "DLC" 
 AND Sales-Rep BEGINS "S"
Sales-Rep Sales-Rep 
WHERE Country BEGINS "EC"
 AND Sales-Rep BEGINS "S"
 BY Country
Country-Post Country-Post 
WHERE Comments CONTAINS "big"   AND Country = "USA" 
 AND Postal-Code = "01730"
Comments Country-Post Country Postal-Code

The following recommendations are intended to help you maximize query performance. They are only recommendations, and you can choose to ignore one or more of them in specific circumstances:

  • Avoid joining range matches with AND.
  • Avoid ORs if any expression on either side of the OR does not use an index (or all its components). Be aware that the AVM must scan all records using the primary index.
  • With word indexes, avoid using AND with two wild card strings, either in the same word index (WHERE comments CONTAINS “fast* & grow*”) or in separate word indexes (WHERE comments CONTAINS “fast*” AND report CONTAINS “ris*”).
  • Avoid WHERE clauses that OR a word index reference and a non-indexed criterion (WHERE comments CONTAINS “computer” OR address2 = “Bedford”).