When to use an index

Indexes improve performance when querying data.

Use an index when:

• You want UltraLite to maintain referential integrity

  An index also affords UltraLite a means of enforcing a uniqueness constraint on the rows in a table. You do not need to add an index for data that is very similar.

• The performance of a particular query is important to your application

  If an index improves performance of a query and the performance of that query is important to your application and is used frequently, then you want to maintain that index. Unless the table in question is extremely small, indexes can improve search performance dramatically. Indexes are typically recommended whenever you search data frequently.

• You have complicated queries

  More complicated queries, (for example, those with JOIN, GROUP BY, and ORDER BY clauses), can yield substantial improvements when an index is used, though it may be harder to determine the degree to which performance has been enhanced. Therefore, test your queries both with and without indexes, to see which yields better performance.

• The size of an UltraLite table is large

  The average time to find a row increases with the size of the table. Therefore, to increase searchability in a very large table, consider using an index. An index allows UltraLite to find rows quickly, but only for columns that are indexed. Otherwise, UltraLite must search every row in the table to see if the row matches the search condition, which can be time consuming in a large table.

• The UltraLite client application is not performing a large amount of insert, update, or delete operations

  Because UltraLite maintains indexes along with the data itself, an index in this context will have an adverse effect on the performance of database operations. For this reason, you should restrict the use of indexes to data that will be queried regularly described in the point above. Maintaining the UltraLite default indexes (indexes for primary keys and for unique constraints) may be enough.

• Use indexes on columns involved in WHERE clauses and/or ORDER BY clause

  These indexes can speed the evaluation of these clauses. In particular, an index helps optimize a multi-column ORDER BY clause, but only when the placement of columns in the index and ORDER BY clauses are exactly the same.