Skip to main content

Database indexes

An index lets the database find rows by a value without reading the whole table. Filters, data grid columns and queries in code that look rows up by an indexed column get faster; every insert and update of the table gets a little slower, because the index has to be kept up to date with it. Index the columns your app actually filters and sorts by, not every column.

Indexes are configured on the Database tab of the entity detail in the App Builder, and in the Database Indexes section of the entity editor in the Jetveo IDE.

Single-column indexes​

An attribute is indexed on its own by its Is indexed flag, set in the attribute's detail on the Data Definition tab. The Database tab lists every indexed attribute of the entity, including the ones inherited from Aspects, and lets you remove the index of the entity's own attributes; an inherited attribute is changed on its aspect.

A reference that points to a single record — Direct, Direct nullable and Codebook — is always indexed, as its foreign key. The tab shows these as automatic; they cannot be removed.

Composite indexes​

A composite index covers several columns of the entity at once. It serves a query that filters by all of them together — the comments of one record ordered by their creation time, the orders of one customer in one state — far better than separate single-column indexes do.

Click Add composite index and pick its columns in the multiselect. A composite index can hold:

  • attributes of type String, Integer, Decimal, Bool and Date, including attributes inherited from aspects;
  • Direct, Direct nullable and Codebook references.

Document and User attributes and collection references (composition, aggregation, many-to-many) cannot be part of one, because they are not a single column of the entity's table.

Column order​

The order of the columns matters. The database can use a composite index for a query that filters by its first column, by its first two columns, and so on — but not for a query that skips the first column. An index over Customer, State serves "orders of this customer" and "orders of this customer in this state", but not "orders in this state". Put the column you always filter by first.

The columns are kept in the order you picked them; the arrows under the multiselect move a column up or down. The columns and their order are settled while you add the index: once added, an index cannot be changed, only deleted — to change one, delete it and add a new one.

Rules​

  • A composite index has at least two columns; a single column is indexed by its attribute's Is indexed flag.
  • A column can appear in an index only once.
  • Two composite indexes of one entity cannot have the same columns in the same order. The same columns in another order are a different index.

A package that breaks one of these rules does not pass validation.

Changing indexes of a released app​

Adding or removing an index is applied to the existing database by the next migration; the data stays untouched. Renaming the entity or one of the indexed attributes keeps the index as it is.

Deleting an attribute or a reference deletes every composite index it is part of, as a whole: an index that lost a column is no longer the index that was designed. Add a new one over the remaining columns if you still need it. In the Jetveo IDE this applies to the entity's own members; an index over an attribute that was deleted from an aspect is reported as an error on the entity until you remove or change it.