An index is a data structure used internally by a database to speed up
filtering and sorting operations. Most commonly, indexes are declared within
object type declarations and reference a particular property; this will speed
up any query that references that property in a filter
or order by
clause.
While improving query performance, indexes also increase disk and memory usage and slow down insertions and updates. Creating too many indexes may be detrimental; only index properties you often filter or order by.
Below, we are referencing the User.name
property with the dot
notation shorthand: .name
.
type User {
required property name -> str;
index on (.name);
}
By indexing on User.name
, queries that filter by the name
property will
be faster, as the database can lookup a name in the index instead of scanning
through all Users sequentially.
Indexes may be defined using an arbitrary singleton expression that references multiple properties of the enclosing object type.
A singleton expression is an expression that’s guaranteed to return at most
one element. As such, you can’t index on a multi
property.
type User {
required property first_name -> str;
required property last_name -> str;
index on (str_lower(.firstname + ' ' + .lastname));
}
A composite index is an index that references multiple properties. This will
speed up queries that filter or sort on both properties. In EdgeDB, this is
accomplished by indexing on a tuple
of properties.
type User {
required property name -> str;
required property email -> str;
index on ((.name, .email));
}
Link properties can also be indexed.
abstract link friendship {
property strength -> float64;
index on (__subject__@strength);
}
type User {
multi link friends extending friendship -> User;
}
Indexes can be augmented with annotations.
type User {
property name -> str;
index on (.name) {
annotation description := 'Indexing all users by name.';
};
}
Foreign and primary keys
In SQL databases, indexes are commonly used to index primary keys and
foreign keys. In EdgeDB, these fields are automatically indexed; there’s no
need to manually declare them. Moreover, any property with an
exclusive
constraint is also automatically indexed.