# CREATE INDEX

## Syntax

```sql
CREATE INDEX
    index_name
ON
    materialized_view_name 
(
    column_name [, ...]
);
```

## Description

This statement creates an index on the specified column(s) of an existing [Materialized View](/overview/core-concepts/databases.md#_materialized_view). Indexes, when created appropriately, are generally used to improve queries performance. The decision to create an index on a set of columns depends on various factors and should be made based on careful consideration of specific queries, a user normally runs on a Materialized View. Once created, an index is updated after each change in the Materialized View's rows. Moreover, given that an index is a separate relation, its size grows proportional to the size of the Materialized View, it is created on. Therefore, creating an index has an impact on the insertion performance and total storage size of the Materialized View. Primary key column(s) set (if any) are indexed automatically.

## Examples

Assume you create a stream named `pageviews` along with a materialized view named `pagevisits` by the following statements. See [CREATE MATERIALIZED VIEW AS](/reference/sql-syntax/query/materialized-view/create-materialized-view-as.md) for more details on creating materialized views.

```sql
CREATE STREAM pageviews (
  viewtime BIGINT,
  userid VARCHAR,
  pageid VARCHAR
) WITH (
  'topic' = 'pageviews',
  'value.format' = 'json'
);

CREATE MATERIALIZED VIEW
  pagevisits
AS SELECT
  userid, pageid
FROM pageviews
WHERE userid <> 'User_5';
```

The following statement creates an index named `visits_idx` on `pagevisits` using the `userid` column. This index improves the performance of queries that retrieve rows by filtering on `userid` values.

```sql
CREATE INDEX 
  visits_idx 
ON 
  pagevisits (userid);
```

You can also create an index on multiple columns from a materialized view. For example, the statement below creates an index on `pagevisits` using `userid` and `pageid` columns.

{% hint style="info" %}
**Note** The order of columns in an index matters.
{% endhint %}

```sql
CREATE INDEX 
  visits_compound_idx 
ON 
  pagevisits (userid, pageid);
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.deltastream.io/reference/sql-syntax/ddl/create-index.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.