---
title: CREATE INDEX
description: "Use CREATE INDEX to speed up point lookups and highly selective queries in MotherDuck."
---

# CREATE INDEX

The `CREATE INDEX` statement creates an [Adaptive Radix Tree (ART)](https://duckdb.org/docs/stable/sql/indexes) index on one or more columns. In MotherDuck, indexes speed up point lookups, range queries, and some highly selective joins.

## Syntax

```sql
CREATE [UNIQUE] INDEX [IF NOT EXISTS] <index_name>
ON <table_name> (<column_name> [, ...]);
```

## When to use indexes

Indexes work best for very selective queries that return a small fraction of the table's rows. For example:

- **Point lookups** -- finding a single row by ID or key
- **Highly selective range queries** -- filtering on a narrow range that matches less than ~0.1% of the data
- **Selective joins** -- joining on indexed columns with high selectivity

For broader analytical queries that scan large portions of a table, MotherDuck's columnar storage and zone maps already provide strong performance without indexes.

## Example

```sql
-- Create a table and an index
CREATE TABLE users (id INTEGER, name VARCHAR);
INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
CREATE INDEX idx_user_id ON users(id);

-- Point lookup uses the index
SELECT * FROM users WHERE id = 1;
```

You can verify that the index is being used with the [EXPLAIN](/sql-reference/motherduck-sql-reference/explain/) statement:

```sql
EXPLAIN SELECT * FROM users WHERE id = 1;
-- Shows INDEX_SCAN when the index is used
```

## Constraints

Indexes are also created automatically when you add a `UNIQUE` or `PRIMARY KEY` constraint. This lets you use features like [`INSERT ... ON CONFLICT`](https://duckdb.org/docs/stable/sql/statements/insert#on-conflict-clause) for upserts and deduplication.

```sql
CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  event_name VARCHAR
);

-- Upsert: insert or update on conflict
INSERT INTO events VALUES (1, 'signup')
ON CONFLICT (event_id) DO UPDATE SET event_name = excluded.event_name;
```

## Trade-offs

Indexes slow down `INSERT`, `UPDATE`, and `DELETE` operations because the index must be updated alongside the table data. If your workload is write-heavy and doesn't benefit from selective lookups, skip the index.

ART indexes also need to fit in memory during creation, so they may not be practical for very large columns.

For more details on DuckDB's index implementation, see the [DuckDB Indexes documentation](https://duckdb.org/docs/stable/sql/indexes).