Progress
Database Design
Guide


General Rules for Choosing a Single Index

When the selection criteria do not support multiple index usage, Progress uses these general rules (in this order) to select the most efficient index:

  1. If there is a CONTAINS clause (which is legal only for word indexed fields), use the word index:
  2. 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:
  4. Sample WHERE Clause
    Indexes Used
    WHERE Customer.Cust-Num = 10 
     AND Customer.Sales-Rep = "DR" 
    
    Cust-Num

  5. Use the index with the most active equality matches. Equality matches are active if:
    • They apply to successive, leading index components
    • AND

    • 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

  6. 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 four properties:
    • The component is the first or only one in the index
    • All preceding components in the index key have active equality matches
    • 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

  7. Use the index with the most sort matches. (All sort matches are active.)
  8. 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

  9. Use the index that comes first alphabetically. That is, if there is a tie—if multiple indexes have the same number of active equality, range, and/or sort matches—use the alphabet to decide:
  10. 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

  11. Use the primary index:
  12. 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


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095