Data Types and the Use of Database Indexes

The software automatically determines if it can use indexes when it looks for records in the database. If the software cannot use an available index, each record in the record-containing objects must be scanned (a potentially slower process).

In some cases, the software cannot use an available index if the data type of the indexed field and the data type of the comparison value are incompatible.

Data Type Compatibility in Indexed Database Searches

Data Type of Indexed Field

Required Data Type of Comparison Value (for index to be used)

CHAR, VARCHAR

CHAR, VARCHAR

ALPHA, VARALPHA

CHAR, VARCHAR, ALPHA, VARALPHA

Any number data type, DATE

Any data type

Example

If you enter the command

find Employees where EmpNum = 156

and EmpNum is an Indexed field of a character type, you are asking to compare the number 156 to a character field.

In this case, the software does not use the available index, because to do so would produce an incorrect result. The index is sorted internally, based on character comparisons. The literal 156 is implicitly a VASTINT number and cannot be effectively compared to character values.

Instead, the software performs the FIND by checking the 156 against the individual values of EmpNum. Speed of execution is slower than if the index could be used.

To ensure that the index is used, modify the command to read

find Employees where EmpNum = '156'

In this case, the literal, because it is enclosed in quotation marks, is implicitly a character data type (CHAR), and can be compared to the indexed EmpNum values, which are explicitly character type.