Indexes for search performance

This section describes how to manage indexes. For query performance purposes, it is important to also understand how the indexes are used by the Cypher® planner. Refer to Query tuning for examples and in-depth discussions on how query plans result from different index and query scenarios. See specifically The use of indexes for examples of how various index scenarios result in different query plans.

For information on index configuration and limitations, refer to Operations Manual → Index configuration.

Indexes (types and limitations)

A database index is a redundant copy of some of the data in the database for the purpose of making searches of related data more efficient. This comes at the cost of additional storage space and slower writes, so deciding what to index and what not to index is an important and often non-trivial task.

Once an index has been created, it will be managed and kept up to date by the DBMS. Neo4j will automatically pick up and start using the index once it has been created and brought online.

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.

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

Differences in the usage patterns between composite and single-property indexes are described in Composite index limitations.

The following is true for indexes:

  • Best practice is to 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.

  • Index creation is by default not idempotent, and an error will be thrown if you attempt to create the same index twice. Using the keyword IF NOT EXISTS makes the command idempotent, and no error will be thrown if you attempt to create the same index twice.

Syntax

Table 1. Syntax for managing indexes
Command Description Comment 
CREATE [BTREE] INDEX [index_name] [IF NOT EXISTS]
FOR (n:LabelName)
ON (n.propertyName)
[OPTIONS "{" option: value[, ...] "}"]

Create a single-property index on nodes.

Index provider and configuration can be specified using the OPTIONS clause.

Best practice is to 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.

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.

 
CREATE [BTREE] INDEX [index_name] [IF NOT EXISTS]
FOR ()-"["r:TYPE_NAME"]"-()
ON (r.propertyName)
[OPTIONS "{" option: value[, ...] "}"]

Create a single-property index on relationships.

Index provider and configuration can be specified using the OPTIONS clause.

 
CREATE [BTREE] INDEX [index_name] [IF NOT EXISTS]
FOR (n:LabelName)
ON (n.propertyName_1,
    n.propertyName_2,
    …
    n.propertyName_n)
[OPTIONS "{" option: value[, ...] "}"]

Create a composite index on nodes.

Index provider and configuration can be specified using the OPTIONS clause.

 
CREATE [BTREE] INDEX [index_name] [IF NOT EXISTS]
FOR ()-"["r:TYPE_NAME"]"-()
ON (r.propertyName_1,
    r.propertyName_2,
    …
    r.propertyName_n)
[OPTIONS "{" option: value[, ...] "}"]

Create a composite index on relationships.

Index provider and configuration can be specified using the OPTIONS clause.

 
CREATE LOOKUP INDEX [index_name] [IF NOT EXISTS]
FOR (n)
ON EACH labels(n)

Create a node label lookup index.

Token lookup indexes do not support any OPTIONS values.

 
CREATE LOOKUP INDEX [index_name] [IF NOT EXISTS]
FOR ()-"["r"]"-()
ON [EACH] type(r)

Create a relationship type lookup index.

Token lookup indexes do not support any OPTIONS values.

 
DROP INDEX index_name [IF EXISTS]

Drop an index, either a b-tree, fulltext, or token lookup index.

The command is optionally idempotent, with the default behavior to throw an error if you attempt to drop the same index twice. With IF EXISTS, no error is thrown and nothing happens should the index not exist.

 
SHOW [ALL|BTREE|FULLTEXT|LOOKUP] INDEX[ES]
    [YIELD { * | field[, ...] } [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]
    [WHERE expression]
    [RETURN field[, ...] [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]

List indexes in the database, either all or filtered on b-tree, fulltext, or token lookup indexes.

When using the RETURN clause, the YIELD clause is mandatory and may not be omitted.

 
DROP INDEX ON :LabelName(propertyName)

Drop a single-property index on nodes without specifying a name.

This syntax is deprecated.

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

Drop a composite index on nodes without specifying a name.

Creating an index requires the CREATE INDEX privilege, while dropping an index requires the DROP INDEX privilege and listing indexes require the SHOW INDEX privilege.

Planner hints and the USING keyword describes how to make the Cypher planner use specific indexes (especially in cases where the planner would not necessarily have used them).

Composite index limitations

Like single-property indexes, composite indexes support all predicates:

  • equality check: n.prop = value

  • list membership check: n.prop IN list

  • existence check: n.prop IS NOT NULL

  • range search: n.prop > value

  • prefix search: STARTS WITH

  • suffix search: ENDS WITH

  • substring search: CONTAINS

For details about each operator, see Operators.

However, predicates might be planned as existence check and a filter. For most predicates, this can be avoided by following these restrictions:

  • If there is any equality check and list membership check predicates, they need to be for the first properties defined by the index.

  • There can be up to one range search or prefix search predicate.

  • There can be any number of existence check predicates.

  • Any predicate after a range search, prefix search or existence check predicate has to be an existence check predicate.

However, the suffix search and substring search predicates are always planned as existence check and a filter and any predicates following after will therefore also be planned as such.

For example, an index on nodes with :Label(prop1,prop2,prop3,prop4,prop5,prop6) and predicates:

WHERE n.prop1 = 'x' AND n.prop2 = 1 AND n.prop3 > 5 AND n.prop4 < 'e' AND n.prop5 = true AND n.prop6 IS NOT NULL

will be planned as:

WHERE n.prop1 = 'x' AND n.prop2 = 1 AND n.prop3 > 5 AND n.prop4 IS NOT NULL AND n.prop5 IS NOT NULL AND n.prop6 IS NOT NULL

with filters on n.prop4 < 'e' and n.prop5 = true, since n.prop3 has a range search predicate.

And an index on nodes with :Label(prop1,prop2) with predicates:

WHERE n.prop1 ENDS WITH 'x' AND n.prop2 = false

will be planned as:

WHERE n.prop1 IS NOT NULL AND n.prop2 IS NOT NULL

with filters on n.prop1 ENDS WITH 'x' and n.prop2 = false, since n.prop1 has a suffix search predicate.

Composite indexes require predicates on all properties indexed. If there are predicates on only a subset of the indexed properties, it will not be possible to use the composite index. To get this kind of fallback behavior, it is necessary to create additional indexes on the relevant sub-set of properties or on single properties.

Creating indexes

Create a single-property index for nodes

A named index on a single property for all nodes that have a particular label can be created with CREATE INDEX index_name FOR (n:Label) ON (n.property). Note that the index is not immediately available, but is created in the background.

Query
CREATE INDEX node_index_name FOR (n:Person) ON (n.surname)

Note that the index name needs to be unique.

Result
+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

Create a single-property index for relationships

A named index on a single property for all relationships that have a particular relationship type can be created with CREATE INDEX index_name FOR ()-[r:TYPE]-() ON (r.property). Note that the index is not immediately available, but is created in the background.

Query
CREATE INDEX rel_index_name FOR ()-[r:KNOWS]-() ON (r.since)

Note that the index name needs to be unique.

Result
+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

Create a single-property index only if it does not already exist

If it is unknown if an index exists or not but we want to make sure it does, we add IF NOT EXISTS.

Query
CREATE INDEX node_index_name IF NOT EXISTS FOR (n:Person) ON (n.surname)

Note that the index will not be created if there already exists an index with the same name, same schema or both.

Result
+--------------------------------------------+
| No data returned, and nothing was changed. |
+--------------------------------------------+

Create a single-property index with specified index provider

To create a single property index with a specific index provider, the OPTIONS clause is used. Valid values for the index provider is native-btree-1.0 and lucene+native-3.0, default if nothing is specified is native-btree-1.0.

Query
CREATE BTREE INDEX index_with_provider FOR ()-[r:TYPE]-() ON (r.prop1) OPTIONS {indexProvider:
  'native-btree-1.0'}

Can be combined with specifying index configuration.

Result
+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

Create a single-property index with specified index configuration

To create a single property index with a specific index configuration, the OPTIONS clause is used. Valid configuration settings are spatial.cartesian.min, spatial.cartesian.max, spatial.cartesian-3d.min, spatial.cartesian-3d.max, spatial.wgs-84.min, spatial.wgs-84.max, spatial.wgs-84-3d.min, and spatial.wgs-84-3d.max. Non-specified settings get their respective default values.

Query
CREATE BTREE INDEX index_with_config FOR (n:Label) ON (n.prop2)
OPTIONS {indexConfig: {`spatial.cartesian.min`: [-100.0, -100.0], `spatial.cartesian.max`: [100.0,
  100.0]}}

Can be combined with specifying index provider.

Result
+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

Create a composite index for nodes

A named index on multiple properties for all nodes that have a particular label — i.e. a composite index — can be created with CREATE INDEX index_name FOR (n:Label) ON (n.prop1, …​, n.propN). Only nodes labeled with the specified label and which contain all the properties in the index definition will be added to the index. Note that the composite index is not immediately available, but is created in the background. The following statement will create a named composite index on all nodes labeled with Person and which have both an age and country property:

Query
CREATE INDEX node_index_name FOR (n:Person) ON (n.age, n.country)

Note that the index name needs to be unique.

Result
+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

Create a composite index for relationships

A named index on multiple properties for all relationships that have a particular relationship type — i.e. a composite index — can be created with CREATE INDEX index_name FOR ()-[r:TYPE]-() ON (r.prop1, …​, r.propN). Only relationships labeled with the specified type and which contain all the properties in the index definition will be added to the index. Note that the composite index is not immediately available, but is created in the background. The following statement will create a named composite index on all relationships labeled with PURCHASED and which have both a date and amount property:

Query
CREATE INDEX rel_index_name FOR ()-[r:PURCHASED]-() ON (r.date, r.amount)

Note that the index name needs to be unique.

Result
+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

Create a composite index with specified index provider and configuration

To create a composite index with a specific index provider and configuration, the OPTIONS clause is used. Valid values for the index provider is native-btree-1.0 and lucene+native-3.0, default if nothing is specified is native-btree-1.0. Valid configuration settings are spatial.cartesian.min, spatial.cartesian.max, spatial.cartesian-3d.min, spatial.cartesian-3d.max, spatial.wgs-84.min, spatial.wgs-84.max, spatial.wgs-84-3d.min, and spatial.wgs-84-3d.max. Non-specified settings get their respective default values.

Query
CREATE INDEX index_with_options FOR (n:Label) ON (n.prop1, n.prop2)
OPTIONS {
 indexProvider: 'lucene+native-3.0',
 indexConfig: {`spatial.wgs-84.min`: [-100.0, -80.0], `spatial.wgs-84.max`: [100.0, 80.0]}
}

Specifying index provider and configuration can be done individually.

Result
+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

Create a node label lookup index

A named token lookup index for all nodes with one or more labels can be created with CREATE LOOKUP INDEX index_name FOR (n) ON EACH labels(n). Note that the index is not immediately available, but is created in the background.

Query
CREATE LOOKUP INDEX node_label_lookup_index FOR (n) ON EACH labels(n)

Note that it can only be created once and that the index name must be unique.

Result
+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

Create a relationship type lookup index

A named token lookup index for all relationships with any relationship type can be created with CREATE LOOKUP INDEX index_name FOR ()-[r]-() ON EACH type(r). Note that the index is not immediately available, but is created in the background.

Query
CREATE LOOKUP INDEX rel_type_lookup_index FOR ()-[r]-() ON EACH type(r)

Note that it can only be created once and that the index name must be unique.

Result
+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

Listing indexes

Listing indexes can be done with SHOW INDEXES, which will produce a table with the following columns:

Table 2. List indexes output
Column Description Default output Full output

id

The id of the index.

+

+

name

Name of the index (explicitly set by the user or automatically assigned).

+

+

state

Current state of the index.

+

+

populationPercent

% of index population.

+

+

uniqueness

Tells if the index is only meant to allow one value per key.

+

+

type

The IndexType of this index (BTREE, FULLTEXT, or LOOKUP).

+

+

entityType

Type of entities this index represents (nodes or relationship).

+

+

labelsOrTypes

The labels or relationship types of this index.

+

+

properties

The properties of this index.

+

+

indexProvider

The index provider for this index.

+

+

options

The options passed to CREATE command.

+

failureMessage

The failure description of a failed index.

+

createStatement

Statement used to create the index.

+

Listing indexes also allows for WHERE and YIELD clauses to filter the returned rows and columns.

The deprecated built-in procedures for listing indexes, such as db.indexes, work as before and are not affected by the SHOW INDEXES privilege.

Listing indexes examples

Listing all indexes

To list all indexes with the default output columns, the SHOW INDEXES command can be used. If all columns are required, use SHOW INDEXES YIELD *.

Query
SHOW INDEXES

One of the output columns from SHOW INDEXES is the name of the index. This can be used to drop the index with the DROP INDEX command.

Result
+------------------------------------------------------------------------------------------------------------------------------------------------+
| id | name             | state    | populationPercent | uniqueness  | type    | entityType | labelsOrTypes | properties    | indexProvider      |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| 4  | "index_58a1c03e" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "NODE"     | ["Person"]    | ["location"]  | "native-btree-1.0" |
| 5  | "index_d7c12ba3" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "NODE"     | ["Person"]    | ["highScore"] | "native-btree-1.0" |
| 3  | "index_deeafdb2" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "NODE"     | ["Person"]    | ["firstname"] | "native-btree-1.0" |
+------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows

Listing indexes with filtering

One way of filtering the output from SHOW INDEXES by index type is the use of type keywords, listed in the syntax table. For example, to show only b-tree indexes, use SHOW BTREE INDEXES. Another more flexible way of filtering the output is to use the WHERE clause. An example is to only show indexes not belonging to constraints.

Query
SHOW BTREE INDEXES WHERE uniqueness = 'NONUNIQUE'

This will only return the default output columns. To get all columns, use SHOW INDEXES YIELD * WHERE …​.

Result
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| id | name             | state    | populationPercent | uniqueness  | type    | entityType     | labelsOrTypes | properties    | indexProvider      |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| 4  | "index_58a1c03e" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "NODE"         | ["Person"]    | ["location"]  | "native-btree-1.0" |
| 6  | "index_c207e3e6" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "RELATIONSHIP" | ["KNOWS"]     | ["since"]     | "native-btree-1.0" |
| 5  | "index_d7c12ba3" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "NODE"         | ["Person"]    | ["highScore"] | "native-btree-1.0" |
| 3  | "index_deeafdb2" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "NODE"         | ["Person"]    | ["firstname"] | "native-btree-1.0" |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows

Deleting indexes

Drop an index

An index can be dropped using the name with the DROP INDEX index_name command. This command can drop b-tree, fulltext, or token lookup indexes. The name of the index can be found using the SHOW INDEXES command, given in the output column name.

Query
DROP INDEX index_name
Result
+-------------------+
| No data returned. |
+-------------------+
Indexes removed: 1

Drop a non-existing index

If it is uncertain if an index exists and you want to drop it if it does but not get an error should it not, use:

Query
DROP INDEX missing_index_name IF EXISTS
Result
+--------------------------------------------+
| No data returned, and nothing was changed. |
+--------------------------------------------+

Deprecated syntax

This syntax does not support dropping relationship property indexes, these can only be dropped by name.

Drop a single-property index

An index on all nodes that have a label and single property combination can be dropped with DROP INDEX ON :Label(property).

Query
DROP INDEX ON :Person(firstname)
Result
+-------------------+
| No data returned. |
+-------------------+
Indexes removed: 1

Drop a composite index

A composite index on all nodes that have a label and multiple property combination can be dropped with DROP INDEX ON :Label(prop1, …​, propN). The following statement will drop a composite index on all nodes labeled with Person and which have both an age and country property:

Query
DROP INDEX ON :Person(age, country)
Result
+-------------------+
| No data returned. |
+-------------------+
Indexes removed: 1