The UltraLite optimizer uses sophisticated optimization strategies when choosing an index for query optimization. Except in the case of simple queries, you cannot easily predetermine which index the optimizer uses to optimize the query performance, or if an index is used at all. As the complexity increases, the index selected depends on the clauses required by your query. In some cases, the presence of a FOR READ ONLY clause may cause the optimizer to choose a direct table scan instead of an index to yield better query performance.

When optimizing a query, the optimizer looks at the requirements of the query and checks if there are any indexes that it can use to improve performance. If performance cannot be improved with any index, then the optimizer does not scan one: either a temporary table or a direct page scan is used instead. Therefore, you may need to experiment with your indexes and frequently check the generated execution plans to ensure that:

For complex queries, knowing which index is used is even less predictable. For example, when a query contains a WHERE predicate and a GROUP BY clause in addition to an ORDER BY clause, one index alone might not satisfy the search conditions of this query. So, if you have created an index to meet the selectivity requirements of the WHERE predicate, you may find that the optimizer does not actually use it. Instead, the optimizer may use an index that offers better performance for the ORDER BY conditions because this clause could require the most processing.

Checking the execution plan

You can check the execution plan either programmatically with the appropriate API call or in the Plan Viewer in Interactive SQL: