UltraLite indexes

An index is a set of pointers to rows in a table based on the order of the values of data in one or more table columns. The index is a database object that is maintained automatically by UltraLite after it has been created. When UltraLite optimizes a query, it scans existing indexes to see if one exists for the table(s) named in the query. If it can help UltraLite return rows more quickly, the index is used. If you are using the UltraLite Table API in your application, you can specify an index that helps determine the order in which rows are traversed.

Tip

Indexes can improve the performance of a query—especially for large tables. To see whether a query is using a particular index, you can check the execution plan with Interactive SQL.

Alternatively, your UltraLite applications can include PreparedStatement objects which have a method to return plans.

UltraLite supports the following indexes. These indexes can be single or multi-column (also known as composite indexes). You cannot index LONG VARCHAR or LONG BINARY columns.

Index	Characteristics
Primary key	Required. An instance of a unique key. You can only have one primary key. Values in the indexed column or columns must be unique and cannot be NULL.
Foreign key¹	Optional. Values in the indexed column or columns can be duplicated. Nullability depends on whether the column was created to allow NULL. Values in the foreign key columns must exist in the table being referenced
Unique key²	Optional. Values in the indexed column or columns must be unique and cannot be NULL.
Non-unique index	Optional. Values in the indexed column or columns can be duplicated and can be NULL.
Unique index	Optional. Values in the indexed column or columns cannot be duplicated and can be NULL.

¹ A foreign key can reference either a primary key or a unique key.

² Also known as a unique constraint.

About composite indexes

Multi-column indexes are sometimes called composite indexes. Additional columns in an index can allow you to narrow down your search, but having a two-column index is not the same as having two separate indexes. For example, the following statement creates a two-column composite index:

CREATE INDEX name
ON Employees ( Surname, GivenName );

A composite index is useful if the first column alone does not provide high selectivity. For example, a composite index on Surname and GivenName is useful when many employees have the same surname. A composite index on EmployeeID and Surname would not be useful because each employee has a unique ID, so the column Surname does not provide any additional selectivity.

UltraLite indexes

Tip

About composite indexes

See also