Indexes for search performance

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Because of the cost of additional storage space and slower writes, deciding what to index and what not to index is an important and often non-trivial task.

Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.

Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

Best practice when creating an Index

  • Give the index a name when it is created. If the index is not explicitly named, it will get an auto-generated name.

  • The index name must be unique among both indexes and constraints.

    ../_images/index1.png

  • The command is optionally idempotent, with the default behavior to throw an error if you attempt to create the same index twice. With IF NOT EXISTS, no error is thrown and nothing happens should an index with the same name, schema or both already exist. It may still throw an error if conflicting constraints exist, such as constraints with the same name or schema.

A list of indexes can be retrieved using the SHOW INDEXES command.

Composite vs. Single Property

Cypher enables the creation of indexes on one or more properties for all nodes or relationships that have a given label or relationship type:

  • An index that is created on a single property for any given label or relationship type is called a single-property index.

    The code below sets an index on a property of a node with a particular Label.

CREATE INDEX [index_name] [IF NOT EXISTS]
FOR (n:LabelName)
ON (n.propertyName)

To delete the index :

DROP INDEX ON :LabelName(propertyName)

  • An index that is created on more than one property for any given label or relationship type is called a composite index.

Composite index on a node

CREATE INDEX [index_name] [IF NOT EXISTS]
FOR (n:LabelName)
ON (n.propertyName_1,n.propertyName_2)

Composite index on a relationship

CREATE INDEX [index_name] [IF NOT EXISTS]
FOR ()-"["r:TYPE_NAME"]"-()
ON (r.propertyName_1,r.propertyName_2)

To delete a composite index.

DROP INDEX ON :LabelName (n.propertyName_1,n.propertyName_2)

Last change: Oct 30, 2023