A WHERE clause can be in a command, a rule, or a lookup expression. If a WHERE clause has only one condition, and if the conditional operator following the indexed column is =, IS NULL, BETWEEN, or has a range (e.g. ColumnA > value and ColumnA < value), R:BASE uses the index on the column.
The BETWEEN operator doesn't use the index on a data type that must be hashed. So if the operator is BETWEEN, the data type of the indexed column must be DATE, TIME, INTEGER, REAL, or TEXT with a defined length of 4 or less.
R:BASE doesn't use the index if the WHERE clause contains a wildcard character or an expression. To make R:BASE use the index, replace expressions with constants or dotted variables and get rid of the wildcards.
When a WHERE clause contains more than one condition and all conditions are combined with AND, the clause must have at least one indexed column that uses equal (=), IS NULL, BETWEEN, or a range if you want R:BASE to use indexes. Under these conditions, R:BASE chooses the condition that places the greatest restriction on the WHERE clause for the indexed search. R:BASE uses the first of two conditions when both are at the same level of restriction. Below are the four levels of restriction, in order, for determining what index to use to find rows within a table:
•= is most restrictive
•IS NULL is less restrictive (unless a not NULL (partial) index is defined, or a "BETWEEN range" or "data range" is earlier in the WHERE clause)
•BETWEEN is less restrictive (R:BASE does not use indexes on BETWEEN when the command allows data modifications.)
•data range is least restrictive
In a newly packed database (to make the indexes complete) with DEBUG turned ON, the #TABLEORDER variable value may be reviewed to determine the column order used for the query.
SELECT ALL FROM InvoiceReceive WHERE InvRecID BETWEEN 1476558 AND 1476567 AND CustQrID IS NULL
SHOW VAR #TABLEORDER
InvoiceReceive.InvRecID
The key for InvRecID was used because it is earlier in the WHERE clause than the IS NULL.
SELECT ALL FROM InvoiceReceive WHERE CustQrID IS NULL AND InvRecID BETWEEN 1476558 AND 1476567
SHOW VAR #TABLEORDER
InvoiceReceive.CustQrID
The key for CustQrID was used as no BETWEEN or range conditions are in front of it.
The better index should be placed at the beginning of the WHERE clause (InvRecID is better than CustQrID) when one is a IS NULL and the other is a BETWEEN or range.
