Rushmore cannot take advantage of all indexes. If you use a FOR clause in the INDEX command, Rushmore cannot use the index for optimization. For example, because it contains a FOR clause, this statement cannot be optimized, INDEX ON ORDNUM FOR DISCOUNT > 10 TAG ORDDISC.

Similarly, Rushmore cannot use an index created with a NOT condition. For example, the this expression can be optimized, INDEX ON DELETED() TAG DEL,But this one cannot, INDEX ON NOT DELETED() TAG NOTDEL.

In the special case that you want to exclude delete records from a query, using an index, as in the first example earlier, will speed up operations when you've set SET DELETED to ON.

Data retrieval operations proceed without Rushmore optimization in the following situations:

• When Rushmore cannot optimize the FOR clause expressions in a potentially optimizable command.
  
  
• When a command that might benefit from Rushmore contains a WHILE clause.
  
  
• When memory is low. Data retrieval continues, but is not optimized.
  
  

A basic optimizable expression takes one of the two following forms:

В	Copy Code
eIndex relOp eExp

-or-

В	Copy Code
eExpr relOp eIndex

A basic optimizable expression has the following characteristics:

• eIndex exactly matches the expression on which an index is constructed.
  
  
• eExpr is any expression and can include variables and fields from other unrelated tables.
  
  
• relOp is one of the following relational operators: <, >, =, <=, >=, <>, #, ==, or !=. You can also use the ISNULL(В ), BETWEEN(В ), or INLIST(В ) functions (or their SQL equivalents such as IS NULL, and so on).
  
  

You can use BETWEEN(В ) or INLIST(В ) in the following two forms:

В	Copy Code
BETWEEN(eIndex, eExpr, eExpr)

-or-

В	Copy Code
INLIST(eIndex, eExpr [, eExpr, eExpr, ...])

Note:
ISBLANK() and EMPTY() are not optimizable by Rushmore.

If you create the indexes firstname, custno, UPPER(lastname), and hiredate, each of the following expressions is optimizable:

В	Copy Code
firstname = "Fred" custno >= 1000 UPPER(lastname) = "SMITH" hiredate < {^1997-12-30}

An optimizable expression can contain variables and functions that evaluate to a specific value. For example, using the index addr, if you issue the command STORE "WASHINGTON AVENUE" TO cVar, then the following statements are also basic optimizable expressions:

В	Copy Code
ADDR = cVar ADDR = SUBSTR(cVar,8,3)

It is important to understand when queries will be optimized and when they will not. Visual FoxPro optimizes search conditions by looking for an exact match between the left side of a filter expression and an index key expression. Therefore, Rushmore can optimize an expression only if you search against the exact expression used in an index.

For example, imagine that you've just created a table and are adding the first index using a command such as the following:

В	Copy Code
USE CUSTOMERS INDEX ON UPPER(cu_name) TAG name

The following command is not optimizable, because the search condition is based on the field cu_name only, not on an expression that is indexed:

В	Copy Code
SELECT * FROM customers WHERE cu_name ="ACME"

Instead, you should create an optimizable expression using a command such as the following, in which the expression for which you are searching exactly matches an indexed expression:

В	Copy Code
SELECT * FROM customers WHERE UPPER(cu_name) = "ACME"

Tip:
To determine the level of Rushmore optimization being used, call SYS(3054).

You can combine simple or complex expressions based on the FOR clause or WHERE clause to increase data retrieval speed, if the FOR expressions have the characteristics of basic optimizable expressions.

Basic expressions might be optimizable. You can combine basic expressions using the AND, OR, and NOT logical operators to form a complex FOR clause expression that might also be optimizable. An expression created with a combination of optimizable basic expressions is fully optimizable. If one or more of the basic expressions are not optimizable, the complex expression might be partially optimizable or not optimizable at all.

A set of rules determines if an expression composed of basic optimizable or non-optimizable expressions is fully optimizable, partially optimizable, or not optimizable. The following table summarizes Rushmore query optimization rules.

Combining Basic Expressions

You can use the AND operator to combine two optimizable expressions into one fully optimizable expression:

В	Copy Code
FIRSTNAME = "FRED" AND HIREDATE < {^1997-12-30} && Optimizable

In this example, the OR operator combines a basic optimizable expression with an expression that is not optimizable to create an expression that is not optimizable:

В	Copy Code
FIRSTNAME = "FRED" OR "S" $ LASTNAME && Not optimizable

Using the NOT operator on an optimizable expression creates a fully optimizable expression:

В	Copy Code
NOT FIRSTNAME = "FRED" && Fully optimizable

You can also use parentheses to group combinations of basic expressions.

Just as you can combine basic expressions, you can combine complex expressions to create a more complex expression that is fully optimizable, partially optimizable, or not optimizable. You can then combine these more complex expressions to create expressions that again might be fully or partially optimizable, or not optimizable at all. The following table describes the results of combining these complex expressions. These rules also apply to expressions grouped with parentheses.

Combining Complex Expressions

You can combine fully optimizable expressions with the OR operator to create one expression that is also fully optimizable:

В	Copy Code
* Fully-optimizable expression (FIRSTNAME = "FRED" AND HIREDATE < {^1997-12-30}) ; OR (LASTNAME = "" AND HIREDATE > {^1996-12-30})

To create partially optimizable expressions, combine a fully optimizable expression with an expression that is not optimizable. In the following example, the AND operator is used to combine the expressions:

В	Copy Code
* Partially-optimizable expression (FIRSTNAME = "FRED" AND HIREDATE < {^1997-12-30}) ; AND "S" $ LASTNAME

Partially optimizable expressions can be combined to create one expression that is also partially optimizable:

В	Copy Code
* Partially-optimizable expression (FIRSTNAME = "FRED" AND "S" $ LASTNAME) ; OR (FIRSTNAME = "DAVE" AND "T" $ LASTNAME)

Combining expressions that are not optimizable creates an expression that is also not optimizable:

В	Copy Code
* Expression that is not optimizable ("FRED" $ FIRSTNAME OR "S" $ LASTNAME) ; OR ("MAIN" $ STREET OR "AVE" $ STREET)