# CREATE INDEX

[Read this page in HTML](https://www.scopedb.io/docs/reference/commands/statements/create-index)

Creates an index on a column or expression to speed up matching queries.

> **Note: Automatic indexes and Catalog definitions**
>
> ScopeDB automatically creates **Range** indexes on every column, **Inverted** indexes on `int` and `string` columns, **Path** indexes on `object` and `any` columns, and **Materialized** indexes on [computed columns](https://www.scopedb.io/docs/reference/computed-columns.md). You can also configure **Search** indexes on columns in the [Catalog API](https://www.scopedb.io/docs/api-reference/catalog.md). See the [indexing guide](https://www.scopedb.io/docs/guides/add-indexes.md).

## Syntax

```scopeql
CREATE <index_type> INDEX [ IF NOT EXISTS ] [ <name> ] ON <table_name> (<index_expression>)
[ COMMENT = '<string_literal>' ]
[ WITH ( '<property_name>' = <literal> [, ...] ) ]
```

## Parameters

### `<index_type>`

Choose the type that matches the query operation:

| Type           | Use for                                                                                                                                            |
| -------------- | -------------------------------------------------------------------------------------------------------------------------------------------------- |
| `POINT`        | Equality predicates such as `=` and `IS`, and array membership with `contains`. On `object` and `any`, it also supports filtering on nested paths. |
| `RANGE`        | Range conditions, such as a time interval or latency threshold.                                                                                    |
| `PATTERN`      | String predicates such as `contains`, `starts_with`, `ends_with`, and supported `regexp_like` patterns.                                            |
| `SEARCH`       | Token matching with `search`.                                                                                                                      |
| `MATERIALIZED` | Reusing a stored expression result, such as an extracted payload field.                                                                            |

### `<name>`

An optional index name, unique within the table. If omitted, ScopeDB generates a name from the table and expression. Use an explicit name when you want to refer to the index later.

### `<table_name>` and `<index_expression>`

The table and the column or expression to index. Expressions can include typed field access, such as `payload['order_id']::string`.

## Examples

Create a table for request events:

```scopeql
CREATE TABLE indexed_requests (
    event_id string,
    event_time timestamp,
    message string,
    payload object
)
```

Index event IDs for equality lookups:

```scopeql
CREATE POINT INDEX idx_event_id ON indexed_requests (event_id)
```

Index event time for range filters:

```scopeql
CREATE RANGE INDEX idx_event_time ON indexed_requests (event_time)
```

Index messages for token searches:

```scopeql
CREATE SEARCH INDEX idx_message ON indexed_requests (message)
WITH ('analyzer' = 'log')
```

Index a frequently filtered field inside the payload:

```scopeql
CREATE POINT INDEX idx_order_id ON indexed_requests (payload['order_id']::string)
```

## Usage notes

* ScopeDB chooses which available indexes to use. Indexes require additional storage and work during writes, so add them for queries you run frequently. See the [indexing guide](https://www.scopedb.io/docs/guides/add-indexes.md) for choosing and inspecting indexes.
* Index definitions must reference columns by name rather than positional references such as `$0`.