# Indexes When running queries, you want to get results as soon as possible. In the worst-case scenario, during query execution, all nodes need to be checked to find a match. This is how [the query plan](https://memgraph.com/docs/querying/query-plan) looks when there is no index on the data: ```copy=false memgraph> EXPLAIN MATCH (n:Person {prop: 1}) RETURN n; +-----------------------------------+ | QUERY PLAN | +-----------------------------------+ | " * Produce {n}" | | " * Filter (n :Person), {n.prop}" | | " * ScanAllByLabel (n :Person)" | | " * Once" | +-----------------------------------+ ``` Notice the `ScanAllByLabel` operations. By creating indexes, query execution can be much faster because indexes partition data with a key. When a query is executed, the engine first checks if there is an index, and instead of explicitly checking every node, it can just check the indexed ones, making retrieving indexed data more efficient. The following query creates an index on a property of a certain label: ```cypher CREATE INDEX ON :Person(prop); ``` [The query plan](https://memgraph.com/docs/querying/query-plan) of a query matching that specific node and property: ```nocopy memgraph> EXPLAIN MATCH (n:Person {prop: 1}) RETURN n; +-----------------------------------------------------+ | QUERY PLAN | +-----------------------------------------------------+ | " * Produce {n}" | | " * ScanAllByLabelProperties (n :Person {prop})" | | " * Once" | +-----------------------------------------------------+ ``` The `ScanAllByLabel` operations have been replaced by `ScanAllByLabelProperties`, a more efficient operation. But there are also some downsides to indexing: - each index requires extra storage (memory) - indexes slow down write operations to the database. It is important to choose the right data to create indexes on, as indexing all of the content will not improve the database speed. The structures in the index are dynamically updated on modifications or insertions of new nodes, slowing down the write operations. Indexing won't bring any improvements on properties that are mostly of the same value, as they have no proper distinguishers. For the same reason, indexing certain data types will not bring any significant performance gain. For example, for properties with boolean values, the time will be cut in half. In addition to the indexes mentioned below, Memgraph also supports vector and text indexes. Due to their fundamentally different nature, these indexes have dedicated documentation pages. To learn how to set them up and use them effectively, refer to the [vector search documentation](https://memgraph.com/docs/querying/vector-search) and [text search documentation](https://memgraph.com/docs/querying/text-search). ## Create an index Indexes are not created automatically. You can explicitly create indexes on a data with a specific label or label-property combination using the `CREATE INDEX ON` syntax or `CREATE EDGE INDEX ON` syntax in the case of edge-type indexes. ### Label index > **Info** > > This index supports non-blocking creation: reads continue without interruption, > while writes are briefly paused. For more information, see the [concurrent index > creation](#concurrent-index-creation). To optimize queries that fetch nodes by label, you need to create a label index: ```cypher CREATE INDEX ON :Person; ``` Creating an index will optimize the following type of queries: ```cypher MATCH (n:Person) RETURN n; ``` To drop the label index use: ```cypher DROP INDEX ON :Label; ``` ### Label-property index > **Info** > > This index supports non-blocking creation: reads continue without interruption, > while writes are briefly paused. For more information, see the [concurrent index > creation](#concurrent-index-creation). To optimize queries that fetch nodes with a certain label and property combination, you need to create a label-property index. For the best performance, create an index on properties containing unique integer values. > **Warning** > > Creating a label-property index will not create a label index! For example, to index nodes that are labeled as `:Person` and have a property named `age`: ```cypher CREATE INDEX ON :Person(age); ``` Creating an index will optimize the queries that need to match a specific label and property combination: ```cypher MATCH (n :Person {age: 42}) RETURN n; ``` Index will also optimize queries that filter labels and properties with the `WHERE` clause: ```cypher MATCH (n) WHERE n:Person AND n.age = 42 RETURN n; ``` Be aware that since the filter inside `WHERE` can contain any kind of an expression, the expression can be so complicated that the index doesn't get used. If there is any suspicion that an index isn't used, we recommend writing labels and properties inside the `MATCH` pattern. Label-property indices can also be created directly on properties that are nested within [map properties](https://memgraph.com/docs/fundamentals/data-types#maps). If your schema is already structured using nested JSON-like documents, you can get optimal query index performance without needing to flatten your data using the `.` notation: ```cypher CREATE INDEX ON :Project(delivery.status.due_date) ``` > **Warning** > > This will create an index only on `delivery.status.due_date`. It does not > create an index on `delivery`, nor on `delivery.status`. When querying against nested properties, you **must** use a `WHERE` clause. If you use inline property matching, you will be comparing against the entire map structure, not just the specified property. The following query returns an empty result set because the inline property does not contain the `milestone`. ```cypher CREATE INDEX ON :Project(delivery.status.due_date); CREATE (:Project {delivery: {status: {due_date: date('2025-06-04'), milestone: 'v3.14'}}}); MATCH (proj:Project {delivery: {status: {due_date: date('2025-06-04')}}}) RETURN *; ``` Adding the `milestone` property returns the correct result, but as the `delivery` property is not directly indexed - only the child `due_date` is - the index will still not be used. ```cypher MATCH (proj:Project {delivery: {milestone: 'v3.14', status: {due_date: date('2025-06-04')}}}) RETURN *; ``` ``` +-----------------------------------------------------------------------------+ | proj | +-----------------------------------------------------------------------------+ | (:Project {delivery: {milestone: "v3.14", status: {due_date: 2025-06-04}}}) | +-----------------------------------------------------------------------------+ ``` Instead, you should use a `WHERE` clause to specify just the indexed property, which is the only scenario in which a nested index can be used: ```cypher MATCH (proj:Project) WHERE proj.delivery.status.due_date = date('2025-06-04') RETURN *; ``` ``` +-----------------------------------------------------------------------------+ | proj | +-----------------------------------------------------------------------------+ | (:Project {delivery: {milestone: "v3.14", status: {due_date: 2025-06-04}}}) | +-----------------------------------------------------------------------------+ ``` To drop the label-property index use: ```cypher DROP INDEX ON :Label(property); ``` ### Label-property composite index > **Info** > > This index supports non-blocking creation: reads continue without interruption, > while writes are briefly paused. For more information, see the [concurrent index > creation](#concurrent-index-creation). An index on a label and two or more properties is called a **composite index**. Composite indices optimize queries where multiple properties are commonly used together in filtering clauses, or when uniqueness exists in a combination of node properties, rather than in the individual properties. To create a composite index on nodes labelled `:Person` and having properties `name` and `occupation`: ```cypher CREATE INDEX ON :Person(name, occupation); ``` Such an index can be used to optimise queries which filter against the index's label and some or all of the index's properties: ```cypher MATCH (p:Person) WHERE p.name = "Alice Smith" AND p.occupation = "DevOps Engineer" RETURN *; ``` Without a composite index, you would need to either index on `name` and filter on `occupation` or index on `occupation` and filter on `name`. Having a composite index means queries such as these execute more efficiently because no filtering is required after the index lookup. > **Info** > > There are no restrictions on the number of properties on which a composite index > can be created, but be aware that an index entry will be created for every node > having *any* of these properties. In the following query, an index entry will be created for `OpenMind`, despite it having only two of the properties of the composite index (`name` and `sector`). It should be clear that composite indices are not efficient when they are created on sparsely populated properties: ```cypher CREATE INDEX ON :Company(name, size, location, sector, founded, turnover); CREATE (:Company {name: 'OpenMind', sector: 'AI'}); ``` **The order of properties within the index matters**. The two indices below are distinct, despite them both being for the same label and properties, and this distinction has important performance implications. ```cypher CREATE INDEX ON :Person(name, age, occupation); CREATE INDEX ON :Person(age, occupation, name); ``` This order follows the "leftmost prefix rule", where the leftmost prefix is the subset of index properties starting from the first property. A composite index on properties `(a, b)` can efficiently support queries on property `a`, or on properties `a` and `b` together. However, it cannot efficiently support queries on property `b` alone. Similarly, an index on properties `(b, a)` would help with queries on `b`, or `b` and `a` together, but not on `a` alone. In the case of our index on `(name, age, occupation)`, we can use the index for queries matching all three properties. ```cypher MATCH (p:Person) WHERE p.name = 'Alice Smith' AND p.age = 42 AND p.occupation = 'DevOps Engineer' RETURN *; ``` But we can also use the same index if we are filtering only against `name` and `age`, or even just `name`: ``` cypher MATCH (p:Person) WHERE p.name = 'Alice' AND p.age = 42 RETURN *; MATCH (p:Person) WHERE p.name = 'Alice' RETURN *; ``` However, the index **cannot** be used if we are only filtering against `occupation` or `age`. Formally, for a composite index on `:L(p0, p1, p2, ...)` to be used, a query must match against `:L` and any of the leftmost prefixes of properties (such as: `p0`; or `p0`, `p1`; or `p0`, `p1`, `p2`). This means an index cannot be used in a query if the query does not match a leftmost prefix of properties. The most effective use of an index is in a query which matches only properties from the leftmost prefix of the composite index, as this will need no further filtering. If the query matches a leftmost prefix as well as other non-contiguous properties, the index can be used, but additional filtering is needed. The following table summarises the cases where a composite index on properties `:L(p0, p1, p2)` will and won't be used, and whether additional filtering is required, based on which properties the query matches against: | p0 | p1 | p2 | Example | Uses index | Needs filter | | -- | -- | -- | -------------------------------------------------------- | ---------- | ------------ | | ❌ | ❌ | ❌ | `MATCH (x:L)` | ❌ | ✅ | | ❌ | ❌ | ✅ | `MATCH (x:L) WHERE x.p2 = 1` | ❌ | ✅ | | ❌ | ✅ | ❌ | `MATCH (x:L) WHERE x.p1 = 1` | ❌ | ✅ | | ❌ | ✅ | ✅ | `MATCH (x:L) WHERE x.p1 = 1 AND x.p2 = 1` | ❌ | ✅ | | ✅ | ❌ | ❌ | `MATCH (x:L) WHERE x.p0 = 1` | ✅ | ❌ | | ✅ | ❌ | ✅ | `MATCH (x:L) WHERE x.p0 = 1 AND x.p2 = 1` | ✅ | ✅ | | ✅ | ✅ | ❌ | `MATCH (x:L) WHERE x.p0 = 1 AND x.p1 = 1` | ✅ | ❌ | | ✅ | ✅ | ✅ | `MATCH (x:L) WHERE x.p0 = 1 AND x.p1 = 1 AND x.p2 = 1` | ✅ | ❌ | The best cases here - those where the index can be used with no additional filtering - are those exactly matching on one of the three leftmost prefixes: `p0`; `p0` and `p1`; or `p0`, `p1,` and `p2`. > **Info** > > Because of the leftmost prefix rule, a composite index can subsume a > label+property index. You should be aware, however, that even though an index on > `:L(a, b, c)` can replace an index on `:L(a)`, it may not perform as efficiently > as the single property index. If queries frequently filter on `a` alone, it may > be worth adding an additional `:L(a)` index. When deciding the order of properties within a composite index, you should generally put the property with the highest cardinality (most unique values) first. Alternatively, if queries on property `a` alone are more common than those on `b` alone, prefer `(a, b)` over `(b, a)`. Composite indices may also be created using nested properties. You cannot create a nested composite index for both a property and its parent within the same index. The following is invalid because it contains both `name` and `name.surname`: ```cypher CREATE INDEX ON :Person(name, name.surname) // INVALID ``` However, it is perfectly valid to create nested composite indices on multiple properties underneath the same parent property, provided the parent isn't also specified alone: ```cypher CREATE INDEX ON :Person(name.firstname, name.surname)` ``` To drop the label-property composite index use: ```cypher DROP INDEX ON :Label(property1, property2); ``` ### Descending label-property index By default, a label-property index — single-property or composite — stores entries in ascending order of the indexed properties. Queries that use `ORDER BY … ASC` can then be served directly from the index, skipping the sort step. If your workload frequently sorts in descending order (for example, timestamps, scores, or any "newest/largest first" query), you can create a descending index using the `WITH CONFIG` clause: ```cypher CREATE INDEX ON :Person(age) WITH CONFIG {"order": "DESC"}; ``` A descending index is used to eliminate the sort step for queries that order the indexed property in descending order: ```cypher MATCH (n:Person) WHERE n.age > 30 RETURN n ORDER BY n.age DESC; ``` The benefit is especially large when the query also uses `LIMIT`. Without a matching index, Memgraph must read all candidate rows, sort them, and then return only the top *N*. With a matching descending index, rows are already produced in the desired order, so execution can stop as soon as the first *N* valid entries are yielded — a "top-N" query becomes O(*N*) index reads instead of O(*all matches*) reads plus a sort: ```cypher MATCH (n:Person) RETURN n ORDER BY n.age DESC LIMIT 10; ``` Without the explicit `{"order": "DESC"}` config, the index defaults to ascending order — both of the following are equivalent: ```cypher CREATE INDEX ON :Person(age); CREATE INDEX ON :Person(age) WITH CONFIG {"order": "ASC"}; ``` #### Descending composite index The `{"order": "DESC"}` configuration applies equally to composite indices. In that case, every column in the index is ordered in descending direction: ```cypher CREATE INDEX ON :Person(name, age) WITH CONFIG {"order": "DESC"}; ``` A descending composite index can eliminate the sort for queries where *all* columns are ordered in the same (descending) direction: ```cypher MATCH (p:Person) WHERE p.name > '' RETURN p ORDER BY p.name DESC, p.age DESC; ``` Mixed-direction `ORDER BY` clauses (for example `ORDER BY p.name ASC, p.age DESC`) are not eliminated by the index; use a regular `OrderBy` for those. #### Coexistence with ascending indices Ascending and descending indices on the same label and properties are independent and can coexist. When both exist, Memgraph automatically picks the one whose direction matches the query's `ORDER BY`: ```cypher CREATE INDEX ON :Person(age); CREATE INDEX ON :Person(age) WITH CONFIG {"order": "DESC"}; MATCH (n:Person) WHERE n.age > 30 RETURN n ORDER BY n.age; // uses ASC index MATCH (n:Person) WHERE n.age > 30 RETURN n ORDER BY n.age DESC; // uses DESC index ``` In `SHOW INDEX INFO`, descending indices are listed as `label+property (DESC)` to distinguish them from ascending ones. #### Dropping descending indices `DROP INDEX ON :Label(property)` without a config drops both the ascending and descending index (if they both exist) in a single operation. To drop only one of them, pass the matching config: ```cypher DROP INDEX ON :Person(age) WITH CONFIG {"order": "DESC"}; DROP INDEX ON :Person(age) WITH CONFIG {"order": "ASC"}; ``` The same applies to composite indices: ```cypher DROP INDEX ON :Person(name, age) WITH CONFIG {"order": "DESC"}; ``` > **Info** > > Descending indices are currently supported only in the `IN_MEMORY_TRANSACTIONAL` > storage mode. Creating a descending index in `ON_DISK_TRANSACTIONAL` mode is > not yet supported. ### Global vertex property index > **Info** > > This index supports non-blocking creation: reads continue without interruption, > while writes are briefly paused. For more information, see the [concurrent index > creation](#concurrent-index-creation). A label-property index requires you to know the label up front. But sometimes you just want to find a node by a property, say `uuid`, regardless of what labels the node has. Global vertex-property indices allow you to do exactly this. For example, to create an index on all nodes with a `uuid` property: ```cypher CREATE GLOBAL INDEX ON :(uuid); ``` Once created, queries that search by that property can use the index automatically: ```cypher MATCH (n) WHERE n.uuid = "abc-123" RETURN n; MATCH (n {uuid: "abc-123"}) RETURN n; ``` The planner also considers this index as a fallback when a query specifies a label but no matching label-property index exists. For example, if there is no index on `:Person(uuid)` but a global index on `uuid` exists, `MATCH (n:Person {uuid: "abc-123"}) RETURN n` can use the global index and filter by label afterwards. > **Info** > > When both a label-property index and a global vertex property index could serve > a query, the planner picks whichever has the lower estimated cardinality. In > practice this usually means the label-property index wins, since it's scoped to > a single label. To drop the index: ```cypher DROP GLOBAL INDEX ON :(uuid); ``` ### Edge-type index > **Info** > > This index supports non-blocking creation: reads continue without interruption, > while writes are briefly paused. For more information, see the [concurrent index > creation](#concurrent-index-creation). To optimize queries that fetch only the edges by specific edge-types, you need to create an edge-type index. Creating an edge-type index requires the `--storage-properties-on-edges` flag to be set to true. ```cypher CREATE EDGE INDEX ON :EDGE_TYPE; ``` Creating an edge-type index will optimize the following type of queries: ```cypher MATCH ()-[r:EDGE_TYPE]->() RETURN r; ``` > **Info** > > If you need to access nodes of found edges, you can use the `startNode(r)` and `endNode(r)` functions. > > Named parameters are not supported for edge-type indexes. To drop the edge-type index use: ```cypher DROP EDGE INDEX ON :EDGE_TYPE; ``` ### Edge-type property index > **Info** > > This index supports non-blocking creation: reads continue without interruption, > while writes are briefly paused. For more information, see the [concurrent index > creation](#concurrent-index-creation). To optimize queries that fetch only the edges by specific edge types and properties, you need to create an edge-type property index. Creating an edge-type property index requires the `--storage-properties-on-edges` flag to be set to true. ```cypher CREATE EDGE INDEX ON :EDGE_TYPE(property_name); ``` Creating an edge-type property index will optimize the following type of queries: ```cypher MATCH ()-[r:EDGE_TYPE {property_name: value}]->() RETURN r; ``` > **Info** > > If you need to access nodes of found edges, you can use the `startNode(r)` and `endNode(r)` functions. > > Named parameters are not supported for edge-type property indexes. To drop the edge-type property index use: ```cypher DROP EDGE INDEX ON :EDGE_TYPE(property_name); ``` ### Global edge property index > **Info** > > This index supports non-blocking creation: reads continue without interruption, > while writes are briefly paused. For more information, see the [concurrent index > creation](#concurrent-index-creation). Since edges can have only one edge type, Memgraph provides a way to index all the edges that contain a certain property via the global edge index. The syntax to index all the edges that have a certain property is the following: ```cypher CREATE GLOBAL EDGE INDEX ON :(property_name); ``` Creating a global edge property index will optimize the following type of queries: ```cypher MATCH ()-[r {property_name: value}]->() RETURN r; ``` > **Info** > > If you need to access nodes of found edges, you can use the `startNode(r)` and `endNode(r)` functions. > > Named parameters are not supported for edge-type property indexes. To drop the global edge property index use: ```cypher DROP GLOBAL EDGE INDEX ON :(property_name); ``` ### Point index Point index can be utilized for more performant spatial queries which use `WHERE point.distance()` or `WHERE point.withinbbox()`. To create a point index run the following command: ```cypher CREATE POINT INDEX ON :Label(property); ``` Here is an example of a query using `point.distance()`: ```cypher MATCH (node:Label) WHERE point.distance(point({x:1, y:1}), node.property) < 1 RETURN node; ``` Here are the examples of queries using `point.withinbbox()`: ```cypher MATCH (node:Label) WHERE NOT point.withinbbox(node.property, point({x:1, y:1}), point({x:2, y:2})) RETURN node; ``` ```cypher MATCH (node:Label) WHERE point.withinbbox(node.property, point({x:1, y:1}), point({x:2, y:2})) = false RETURN node; ``` > **Info** > > In Memgraph v2.21 point index couldn't be utilized with the `point.withinbbox()` > function. It could only be utilized with the `point.distance` function and to > use it, the comparison point needed to exist as a variable first. For example, > instead of: > ```cypher MATCH (node:Label) WHERE point.distance(point({x:1, y:1}), node.property) < 1 RETURN node; ``` > you needed to use `WITH` statement: > ```cypher WITH point({x:1, y:1}) AS p MATCH (node:Label) WHERE point.distance(p, node.property) < 1 RETURN node; ``` > From Memgraph v2.22, it is no longer necessary to use the `WITH` statement for > index to be utilized. To drop the point index use: ```cypher DROP POINT INDEX ON :Label(property); ``` ### Analyze graph When multiple label-property indexes exist, the database can sometimes select a non-optimal index due to the data's distribution. The [`ANALYZE GRAPH;`](https://memgraph.com/docs/querying/performance-optimization#analyze-graph) query calculates the distribution of property values so the database can select a more optimal label-property index with the smallest average property value size. The query is run only once after all indexes have been created and data inserted in the database. ### Schema-related procedures You can modify indexes using the [`schema.assert()` procedure](https://memgraph.com/docs/querying/schema#assert). ### Speed comparison Below is a comparison of the same query run without an index and with an index. The query without an index took 0.015 seconds to execute, and the query with an index 0.006 seconds. ```nocopy memgraph> SHOW INDEX INFO; Empty set (0.001 sec) memgraph> MATCH (n:Person) WHERE n.name =~ ".*an$" RETURN n.name; +-------------+ | n.name | +-------------+ | "Lillian" | | "Logan" | | "Susan" | | "Sebastian" | +-------------+ 4 rows in set (0.021 sec) memgraph> CREATE INDEX ON :Person(name); Empty set (0.015 sec) memgraph> MATCH (n:Person) WHERE n.name =~ ".*an$" RETURN n.name; +-------------+ | n.name | +-------------+ | "Lillian" | | "Logan" | | "Susan" | | "Sebastian" | +-------------+ 4 rows in set (0.006 sec) ``` ## Show created indexes To see all the information on the label, label-property, edge-type, edge-type property, global vertex property, global edge property, point indexes and vector indexes, run the following query: ```cypher SHOW INDEX INFO; ``` To be compatible with former Neo4j users, users can also use the following command: ```cypher SHOW INDEXES; ``` The query displays a table holding the information on the indexes created, ordered by index type, label, property and count. To retrieve information about vector indexes, run the following query: ``` SHOW VECTOR INDEX INFO; ``` To be compatible with former Neo4j users, users can also use the following command: ```cypher SHOW VECTOR INDEXES; ``` ## Delete an index Created indexes can be deleted using the following syntax: ```cypher DROP INDEX ON :Label; ``` ```cypher DROP INDEX ON :Label(property); ``` ```cypher DROP INDEX ON :Label(property1, property2); ``` ```cypher DROP GLOBAL INDEX ON :(property_name); ``` ```cypher DROP EDGE INDEX ON :EDGE_TYPE; ``` ```cypher DROP EDGE INDEX ON :EDGE_TYPE(property_name); ``` ```cypher DROP GLOBAL EDGE INDEX ON :(property_name); ``` ```cypher DROP POINT INDEX ON :Label(property); ``` ### Drop all indexes The `DROP ALL INDEXES` clause allows you to delete all indices in your database in a single operation. This includes all types of indices: label indices, label-property indices, edge type indices, edge type-property indices, global vertex property indices, global edge property indices, point indices, text indices, vector indices, and vector edge indices. ```cypher DROP ALL INDEXES; ``` ### Delete all node indexes > **Warning** > > The `schema.assert()` procedure will not drop edge and point indexes. > Our plan is to update it, and you can track the progress on our [GitHub](https://github.com/memgraph/memgraph/issues/2462). > **Info** > > Starting from version 3.6, the `DROP ALL INDEXES` command was introduced and is > the preferred way to remove all indexes, since it works across all index types. > This makes it a better choice than the > [`schema.assert()`](https://memgraph.com/docs/querying/schema#assert) procedure. To delete all indexes, use the [`schema.assert()`](https://memgraph.com/docs/querying/schema#assert) procedure with the following parameters: - `indices_map` = `{}` - `unique_constraints` = map of key-value pairs of all uniqueness constraints in the database - `existence_constraints` = map of key-value pairs of all existence constraints in the database - `drop_existing` = `true` Here is an example of indexes and constraints set in the database: ```cypher CREATE CONSTRAINT ON (n:Person) ASSERT EXISTS (n.name); CREATE CONSTRAINT ON (n:Employee) ASSERT n.id IS UNIQUE; CREATE CONSTRAINT ON (n:Employee) ASSERT n.email IS UNIQUE; CREATE CONSTRAINT ON (n:Employee) ASSERT n.name, n.surname IS UNIQUE; CREATE INDEX ON :Student(id); CREATE INDEX ON :Student; ``` There are three uniqueness and one existence constraint. Additionally, there are two indexes - one label and one label-property index. To delete all indexes, run: ```cypher CALL schema.assert({}, {}, {}, true) YIELD action, key, keys, label, unique RETURN action, key, keys, label, unique; ``` The above query removes all existing indexes because the empty `indices_map` indicates that no indexes should be asserted as existing, while the `drop_existing` set to `true` specifies that all existing indexes should be dropped. Primarily, the [`assert()`](querying/schema#assert) procedure is used to define a schema, but it's also useful if you need to [delete all constraints](https://memgraph.com/docs/fundamentals/constraints#delete-all-constraints) or [delete all node indexes and constraints](https://memgraph.com/docs/querying/schema#delete-all-node-indexes). ## Automatic index creation > **Note** > > Automatic index creation can only be used if the database has `IN_MEMORY_TRANSACTIONAL` [mode enabled](https://memgraph.com/docs/fundamentals/storage-memory-usage#storage-modes). Using the [`storage-automatic-label-index-creation-enabled`](https://memgraph.com/docs/configuration/configuration-settings#storage) and [`storage-automatic-edge-type-index-creation-enabled`](https://memgraph.com/docs/configuration/configuration-settings#storage) flags, it is possible to create label and edge-type indices automatically. Every time the database encounters a label or edge-type that is currently not indexed, it will queue an index creation request that runs asynchronously in the background. Auto-index creation operates with the following characteristics: - **Asynchronous execution**: Index creation runs in dedicated background transaction - **Concurrent creation**: Utilizes the same non-blocking mechanism as manual index creation, allowing user queries to continue uninterrupted ## Recovery Existence and unique [constraints](https://memgraph.com/docs/fundamentals/constraints), and indexes can be recovered in parallel. To enable this behavior, set the [`storage-parallel-schema-recovery` configuration](https://memgraph.com/docs/configuration/configuration-settings#storage) flag to `true`. ## Query optimization ### Analyze graph The `ANALYZE GRAPH` will check and calculate certain properties of a graph so that the database can choose a more optimal index or `MERGE` transaction. Before the introduction of the `ANALYZE GRAPH` query, the database would choose an index solely based on the number of indexed nodes. But if the number of nodes is the only condition, in some cases the database would choose a non-optimal index. Once the `ANALYZE GRAPH` is run, Memgraph analyzes the distribution of property values and can select a more optimal label-property index, the one with the smallest average property value size. The average property value's group size directly represents the database's expected number of hits which can be used to estimate the query's cost. When the average group size is the same, the chi-squared statistic is used to measure how close the distribution of property-value group size is to the uniform distribution. The index with a distribution closest to the uniform distribution is selected. $ \chi^2 = \sum_{i}\frac{(E_i-O_i)^2}{E_i} $ Upon running the `ANALYZE GRAPH` query, Memgraph also check the node degree of every indexed nodes and calculates the average degree. By having these values, Memgraph can make a more optimal `MERGE` expansion and improve performance. It's always better to perform a `MERGE` by expanding from the node that has a lower degree than the connecting node. The `ANALYZE GRAPH;` command should be run only once after all indexes have been created and nodes inserted in the database. In rare situations when one property is set on many more nodes than another property, choosing an index based on average group size and uniform distribution would be misleading. That's why the database always selects the label-property index with >= 10x fewer nodes than the other label-property index. #### Calculate the statistic Run the following query to calculate the statistics: ```cypher ANALYZE GRAPH; ``` The query will iterate over all label and label-property indexes in the database and calculate the average group size, chi-squared statistic and avg degree for each one, then return the following output: | label | property | num estimation nodes | num groups | avg group size | chi-squared value | avg degree | ----- | -------- | -------------------- | ---------- | -------------- | ----------------- | ---------- | index's label | index's property | number of nodes used for estimation | number of distinct values the property contains | average group size of property's values | value of the chi-squared statistic | average degree of the indexed nodes Once the necessary information is obtained, Memgraph can choose the optimal index and `MERGE` expansion. If you don't want to run the analysis on all labels, you can specify which labels to use by adding the labels to the query: ```cypher ANALYZE GRAPH ON LABELS :Label1, :Label2; ``` #### Delete statistic Information about the graph is persistent between instance reruns as is recovered as all the other data, using [snapshots and WAL files](https://memgraph.com/docs/configuration/data-durability-and-backup). If you want the database to ignore information about the average group size, the chi-squared statistic and the average degree, the existing statistic can be deleted by running: ```cypher ANALYZE GRAPH DELETE STATISTICS; ``` The results will contain all label-property indexes that were successfully deleted: | label | property | | ----- | -------- | | index's label | index's property | Specific labels can be specified with the construct `ON LABELS`: ```cypher ANALYZE GRAPH ON LABELS :Label1 DELETE STATISTICS; ``` ### Index hinting When executing a query, Memgraph query planner needs to decide where in the query it should start matching. To get the optimal match, it checks the `MATCH` clause conditions and finds the index that's likely to be the best choice, if there are multiple indexes to choose from. However, the selected index might not always be the best one. Sometimes, there are multiple candidate indexes, and the query planner picks the suboptimal one from a performance point of view. With **index hinting**, you can instruct the planner to use specific index(es) (if possible) in the query that follows. Here is the syntax for such query: ```cypher USING INDEX :Label, :Label2 ...; ``` ```cypher USING INDEX :Label(property) ...; ``` ```cypher USING INDEX :Label(property1, property2) ...; ``` ```cypher USING INDEX :(property) ...; ``` It is also possible to specify multiple hints separated with comma. In that case, the planner will apply the first hint that is applicable for a given match. An example of selecting an index with `USING INDEX`: ``` USING INDEX :Person(name) MATCH (n:Person {name: 'John', gender: 'male'}) RETURN n; ``` > **Warning** > > Overriding planner behavior with index hints should be used with caution, and > only by experienced developers and/or database administrators, as poor index > choice may cause queries to perform poorly. ## Underlying implementation The central part of Memgraph's index data structure is a highly concurrent [skip list](https://en.wikipedia.org/wiki/Skip_list). Skip lists are probabilistic data structures that allow fast search within an ordered sequence of elements. The structure itself is built in layers, where the bottom layer is an ordinary linked list that preserves the order. Each higher level can be imagined as a highway for layers below. The implementation details behind skip list operations are well documented in the literature and are out of the scope of this document. Nevertheless, we believe that it is important for more advanced users to understand the following implications of this data structure (`n` denotes the current number of elements in a skip list): - The average insertion time is `O(log(n))` - The average deletion time is `O(log(n))` - The average search time is `O(log(n))` - The average memory consumption is `O(n)` When it comes to label-property indexes, Memgraph stores a list of specific properties that are used in label-property indexes. This list is ordered to make the search faster. All property types can be ordered. First, they are ordered based on the type, and secondly for values within that type. In the case of composite indices, the list is lexicographically ordered, which means that the pair $(a_1, a_2)$ is ordered before $(b_1, b_2)$ if either $a_1 < b_1$, or $a_1 = b_1$ and $a_2 < b_2$. ### Concurrent index creation Memgraph supports **(almost) fully concurrent index creation** for all skiplist-based indices, including label, label-property, composite, edge-type, edge-type property, global edge property, and global vertex property, with minimal impact on performance. The three-phase implementation begins with a brief **registration phase** that requires `READ ONLY` access. This ensures that all pending write transactions complete before the index creation begins. During this phase, the database waits for ongoing writes to finish (typically milliseconds) while blocking new write transactions. Read queries are not affected by this. Once registered: - Read queries continue uninterrupted throughout index creation - Write operations can proceed concurrently after registration - Existing data is indexed in the background If you encounter write transaction failures during registration, you can increase the wait time using the `--storage-access-timeout-sec` flag or at runtime via: ```cypher SET DATABASE SETTING 'storage.access_timeout_sec' TO 'value'; ``` This extends how long write queries will retry before failing. The system maintains **full MVCC consistency** throughout the process, ensuring transactional integrity. Long-running index operations can be safely cancelled if needed. For complete technical details about the implementation, consistency guarantees, and current limitations, please refer to the [Concurrent Index Creation ADR](https://github.com/memgraph/memgraph/blob/master/ADRs/004_concurrent_index_creation.md).