When the selection criteria do not support multiple index usage (such as for FIND statements), ABL uses these general rules (in this order) to select the most efficient index:

  1. Use the index specified in a USE-INDEX option.
  2. If there is a CONTAINS clause (which is legal only for word indexed fields), use the word index:
    Sample WHERE clause Indexes used 
    WHERE Customer.Comments CONTAINS "big" 
 AND Customer.Country = "Canada"
    		 Comments
  3. If an index is unique, and all of its components are used in active equality matches, use the unique index. It invariably returns 0 or 1 records:
    Sample WHERE clause Indexes used 
    WHERE Customer.Cust-Num = 10 
 AND Customer.Sales-Rep = "DR"
    		 Cust-Num
  4. Use the index with the most active equality matches. Equality matches are active if both of the following conditions are met:
    • They apply to successive, leading index components.
    • They are joined by ANDs (not ORs or NOTs).

    This disqualifies equality matches on, for example, components 2 and 3 of an index with three components, and it disqualifies matches on components 1 and 2 if they surround an OR:

    Sample WHERE clause Indexes used 
    WHERE Customer.Country = "Costa Rica"
 AND Customer.Postal-Code > "3001"
 AND Customer.Sales-Rep BEGINS "S"
    		 Country-Post 
    WHERE Customer.Name = "Harrison" 
 AND Customer.Sales-Rep BEGINS "S"
    		 Name 
    WHERE Customer.Name = "Harrison" 
 AND (Customer.Country = "Finland" 
  OR Customer.Country = "Denmark")
    		 Name
  5. Use the index with the most active range matches. For a range match to be active it must stand alone or be connected to other selection criteria by ANDs. In addition, it must apply to an index component having any one of two properties:
    • The component is the first or only one in the index.
    • All preceding components in the index key have active equality matches.

    The following table provides some index examples:

    Sample WHERE clause Indexes used 
    WHERE Customer.Sales-Rep = "ALH" 
 AND Customer.Country = "Italy" 
 AND Customer.Postal-Code BEGINS "2"
    		 Country-Post 
    WHERE Customer.Contact = "DLC" 
 AND Customer.Sales-Rep BEGINS "S"
    		 Sales-Rep 
    WHERE Customer.Contact = "Ritter" 
 AND Comments CONTAINS "compute*"
    		 Comments
  6. Use the index with the most sort matches (all sort matches are active):
    Sample WHERE clause Indexes used 
    WHERE Customer.Country BEGINS "EC"
 AND Customer.Sales-Rep BEGINS "S"
 BY Country
    		 Country-Post 
    WHERE Customer.Contact = "Wilson"
 AND Customer.Credit-Limit > 2000
 BY Name
    		 Name 
    WHERE Name = "Wilson"
 OR Customer.Credit-Limit = 2000
 BY Sales-Rep
    		 Sales-Rep
  7. If there is a tie—in other words, if multiple indexes have the same number of active equality, range, and/or sort matches—use the index that comes first alphabetically. If the PRIMARY index is one of the indexes in the tie, then use the PRIMARY index:
    Sample WHERE clause Indexes used 
    WHERE Customer.Name = "Samali" 
 AND Customer.Sales-Rep = "BCW"
    		 Name 
    WHERE Customer.Country BEGINS "EC" 
 AND Customer.Sales-Rep BEGINS "B"
Postal-Code
  8. Use the primary index:
    Sample WHERE clause Indexes used 
    WHERE Customer.Contact = "MK" 
 AND (Customer.Sales-Rep BEGINS "S"
   OR Customer.Sales-Rep BEGINS "B")
    		 Cust-Num 
    WHERE Customer.Postal-Code >= "01000" 
 AND Customer.City = "Boston"
    		 Cust-Num 
    WHERE "meaningless expression"
    		 Cust-Num