This section provides a concise, abbreviated summary of the concepts and terminology required to discuss how ABL chooses the most efficient indexes to satisfy a query. For the purposes of index selection, there are three general types of WHERE clause.

Syntax

WHERE searchExpr [ BY field ]

For example:

WHERE Cust-num > 6
WHERE Name "D"  BY Sales-Rep
WHERE Country = "Zimbabwe"
WHERE Comments CONTAINS "Com*"

Syntax

WHERE searchExpr AND searchExpr [ BY field ]

For example:

WHERE Cust-num > 6 AND comments CONTAINS "ASAP"
WHERE Name = "Rogers" AND Postal-Code BEGINS "017"

Syntax

WHERE searchExpr OR searchExpr [ BY field ]

For example:

WHERE Cust-num > 1000 OR Sales-Rep BEGINS "S"
WHERE Postal-Code =< "01500" OR Postal-Code >= "25000"

The optional BYfield clause imposes a sort order on returned records and is called a sort match. A searchExpr typically has one of the following forms:

BY field Sort match
field = expression Equality match
field < / =< / > / >= expression Range match
field BEGINS expression Range match
wordIndexedfield CONTAINS stringExpression Equality (simple string) Range (wild card string) None (>1 string, joined logically)

For more information, see the Record Phrase and FOR statement reference entries in ABL Reference.

Because these expressions effectively select the records to return—and the indexes to use—they are called search conditions. Commonly, but not always, field is an indexed field. Also, a searchExpr can include other searchExpr's joined by ANDs and ORs, forming arbitrarily complex queries.

The Compiler constructs a logical tree from a query and evaluates both sides of each AND or OR, looking for index criteria. ABL counts equality, range, and sort matches (for OR) and uses them to select and bracket indexes. The precise rules are numerous and complex, and it is not important to fully understand their details. The next sections outline the rules in sufficient detail to help you develop a feel for index usage. In addition, you should experiment by coding various queries, compiling them with the XREF option, and examining index usage as reported in the SEARCH lines of the XREF output file.

The index selection examples that follow are based on the sports database.