Using Indexes in Prefix-like Queries

Each index sorts records according to the index key values, ascending or descending. If the index key is a character string, then the lexicographical sorting according to the language specified in the table column description is used.

National lexicographical key value sorting allows an effective evaluation of conditions for equality or inequality of strings used in the query and for sorting records in query result. However it's not suitable for optimizing queries with a prefix condition. Let's consider Czech language and table containing the following data: 'caa', 'czz', 'd', 'cha', 'Ostra', 'Ostrá', 'Ostrava'.

Records are in the same order as above in the national lexicographical index. If this index is used for optimizing the query column LIKE 'c%', then the query result will contain 'caa', 'czz', but not 'cha', because the index pass will end on the 'd' record. Similarly the result of the query column.='Ostra' will contain only the 'Ostra' record, because the 'Ostrá' record will end the process of creating the result and the 'Ostrava' record won't be find.

Therefore national lexicographical indexes can't be used for optimizing queries for prefix.

If it's desired to effectively get results from queries for prefix, you have to define another index in the table first. You have to use to following expression for XYZ column of strings with length D:

CAST(XYZ AS CHAR(D))

Such index sorts records according to ASCII set and may therefore be used for optimizing prefix conditions. If the search with this index should ignore letter case, you have to change the index expression too:

CAST(UPPER(XYZ) AS CHAR(D))

If some table string column is used both in conditions for equalities, inequalities and prefixes, then you have to provide the table with two indexes for effective evaluation of such queries.

Information about Query Optimization and Evaluation

Database Table Indexes

Using Indexes not Containing NULL Values