DEFINE INDEX statement
Just like in other databases, SurrealDB uses indexes to help optimize query performance. An index can consist of one or more fields in a table and can enforce a uniqueness constraint. If you don't intend for your index to have a uniqueness constraint, then the fields you select for your index should have a high degree of cardinality, meaning that there is a high amount of diversity between the data in the indexed table records.
- You must be authenticated as a root, namespace, or database user before you can use the
- You must select your namespace and database before you can use the
DEFINE INDEX @name ON [ TABLE ] @table [ FIELDS | COLUMNS ] @fields [ UNIQUE | SEARCH ANALYZER @analyzer [ BM25 [(@k1, @b)] ] [ HIGHLIGHTS ] ]
How to create a unique index for the email address field on a user table.
-- Make sure that email addresses in the user table are always unique DEFINE INDEX userEmailIndex ON TABLE user COLUMNS email UNIQUE;
How to create a non-unique index for an age field on a user table.
-- optimise queries looking for users of a given age DEFINE INDEX userAgeIndex ON TABLE user COLUMNS age;
How to create a full-text search index for a name field on a user table.
-- Allow full-text search queries on the name of the user DEFINE INDEX userNameIndex ON TABLE user COLUMNS name SEARCH ANALYZER ascii BM25 HIGHLIGHTS;