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.