Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
Indexes improve performance when querying data.
Use an index when:
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.
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.
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 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.
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.
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.