# CREATE INDEX

The `CREATE INDEX` statement creates an index for a table. Indexes improve your database's performance by helping SQL locate data without having to look through every row of a table.

The following types cannot be included in an index key, but can be stored (and used in a covered query) using the `STORING` or `COVERING` clause:

- `JSONB`
- `ARRAY`
- The computed `TUPLE` type, even if it is constructed from indexed fields

To create an index on the schemaless data in a `JSONB` column, use an inverted index.

Note:

Indexes are automatically created for a table's `PRIMARY KEY` and `UNIQUE` columns. When querying a table, CockroachDB uses the fastest index. For more information about that process, see Index Selection in CockroachDB.

Note:

This statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

## Required privileges

The user must have the `CREATE` privilege on the table.

## Synopsis

Standard index:

Inverted index:

## Parameters

Parameter | Description
--- | ---
`UNIQUE` | Apply the `UNIQUE` constraint to the indexed columns. This causes the system to check for existing duplicate values on index creation. It also applies the `UNIQUE` constraint at the table level, so the system checks for duplicate values when inserting or updating data.
`INVERTED` | Create an inverted index on the schemaless data in the specified `JSONB` column. You can also use the PostgreSQL-compatible syntax `USING GIN`. For more details, see Inverted Indexes.
`IF NOT EXISTS` | Create a new index only if an index of the same name does not already exist; if one does exist, do not return an error.
`opt_index_name` `index_name` | The name of the index to create, which must be unique to its table and follow these identifier rules. If you do not specify a name, CockroachDB uses the format `<table>_<columns>_key/idx`. `key` indicates the index applies the `UNIQUE` constraint; `idx` indicates it does not. Example: `accounts_balance_idx`
`table_name` | The name of the table you want to create the index on.
`USING name` | An optional clause for compatibility with third-party tools. Accepted values for `name` are `btree` and `gin`, with `btree` for a standard secondary index and `gin` as the PostgreSQL-compatible syntax for an inverted index on schemaless data in a `JSONB` column.
`column_name` | The name of the column you want to index.
`ASC` or `DESC` | Sort the column in ascending (`ASC`) or descending (`DESC`) order in the index. How columns are sorted affects query results, particularly when using `LIMIT`. Default: `ASC`
`STORING ...` | Store (but do not sort) each column whose name you include. For information on when to use `STORING`, see Store Columns. Note that columns that are part of a table's `PRIMARY KEY` cannot be specified as `STORING` columns in secondary indexes on the table. `COVERING` and `INCLUDE` are aliases for `STORING` and work identically.
`opt_interleave` | You can potentially optimize query performance by interleaving indexes, which changes how CockroachDB stores your data. Note: Hash-sharded indexes cannot be interleaved.
`opt_partition_by` | An enterprise-only option that lets you define index partitions at the row level.
`USING HASH WITH BUCKET COUNT` | New in v20.1: Creates a hash-sharded index with `n_buckets` number of buckets. Note: To enable hash-sharded indexes, set the `experimental_enable_hash_sharded_indexes` session variable to `on`.
`CONCURRENTLY` | New in v20.1: Optional, no-op syntax for PostgreSQL compatibility. All indexes are created concurrently in CockroachDB.

## Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with `SHOW JOBS`.

## Examples

### Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` dataset preloaded:

    $ cockroach demo

### Create standard indexes

To create the most efficient indexes, we recommend reviewing:

- Indexes: Best Practices
- Index Selection in CockroachDB

#### Single-column indexes

Single-column indexes sort the values of a single column.

    > CREATE INDEX ON users (name);

Because each query can only use one index, single-column indexes are not typically as useful as multiple-column indexes.

#### Multiple-column indexes

Multiple-column indexes sort columns in the order you list them.

    > CREATE INDEX ON users (name, city);

To create the most useful multiple-column indexes, we recommend reviewing our best practices.

#### Unique indexes

Unique indexes do not allow duplicate values among their columns.

    > CREATE UNIQUE INDEX ON users (name, id);

This also applies the `UNIQUE` constraint at the table level, similarly to `ALTER TABLE`. The above example is equivalent to:

    > ALTER TABLE users ADD CONSTRAINT users_name_id_key UNIQUE (name, id);

### Create inverted indexes

Inverted indexes can be created on schemaless data in a `JSONB` column.

    > CREATE INVERTED INDEX ON promo_codes (rules);

The above example is equivalent to the following PostgreSQL-compatible syntax:

    > CREATE INDEX ON promo_codes USING GIN (rules);

### Store columns

Storing a column improves the performance of queries that retrieve (but do not filter) its values.

    > CREATE INDEX ON users (city) STORING (name);

However, to use stored columns, queries must filter another column in the same index. For example, SQL can retrieve `name` values from the above index only when a query's `WHERE` clause filters `city`.

### Change column sort order

To sort columns in descending order, you must explicitly set the option when creating the index. (Ascending order is the default.)

    > CREATE INDEX ON users (city DESC, name);

Note that how a column is ordered in the index will affect the ordering of the index keys, and may affect the efficiency of queries that include an `ORDER BY` clause.

### Query specific indexes

Normally, CockroachDB selects the index that it calculates will scan the fewest rows. However, you can override that selection and specify the name of the index you want to use. To find the name, use `SHOW INDEX`.

    > SHOW INDEX FROM users;

      table_name |   index_name   | non_unique | seq_in_index | column_name | direction | storing | implicit
    +------------+----------------+------------+--------------+-------------+-----------+---------+----------+
      users      | primary        |   false    |            1 | city        | ASC       |  false  |  false
      users      | primary        |   false    |            2 | id          | ASC       |  false  |  false
      users      | users_name_idx |    true    |            1 | name        | ASC       |  false  |  false
      users      | users_name_idx |    true    |            2 | city        | ASC       |  false  |   true
      users      | users_name_idx |    true    |            3 | id          | ASC       |  false  |   true
    (5 rows)

    > SELECT name FROM users@users_name_idx WHERE city='new york';

            name
    +------------------+
      Catherine Nelson
      Devin Jordan
      James Hamilton
      Judy White
      Robert Murphy
    (5 rows)

### Create a hash-sharded secondary index

For performance reasons, we discourage indexing on sequential keys. If, however, you are working with a table that must be indexed on sequential keys, you should use hash-sharded indexes. Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range hotspots and improving write performance on sequentially-keyed indexes at a small cost to read performance.

    > CREATE TABLE events (
        product_id INT8,
        owner UUID,
        serial_number VARCHAR,
        event_id UUID,
        ts TIMESTAMP,
        data JSONB,
        PRIMARY KEY (product_id, owner, serial_number, ts, event_id)
    );

    > SET experimental_enable_hash_sharded_indexes=on;

    > CREATE INDEX ON events(ts) USING HASH WITH BUCKET_COUNT=8;

    > SHOW INDEX FROM events;

      table_name |               index_name               | non_unique | seq_in_index |       column_name        | direction | storing | implicit
    -------------+----------------------------------------+------------+--------------+--------------------------+-----------+---------+-----------
      events     | primary                                |   false    |            1 | product_id               | ASC       |  false  |  false
      events     | primary                                |   false    |            2 | owner                    | ASC       |  false  |  false
      events     | primary                                |   false    |            3 | serial_number            | ASC       |  false  |  false
      events     | primary                                |   false    |            4 | ts                       | ASC       |  false  |  false
      events     | primary                                |   false    |            5 | event_id                 | ASC       |  false  |  false
      events     | events_crdb_internal_ts_shard_8_ts_idx |    true    |            1 | crdb_internal_ts_shard_8 | ASC       |  false  |  false
      events     | events_crdb_internal_ts_shard_8_ts_idx |    true    |            2 | ts                       | ASC       |  false  |  false
      events     | events_crdb_internal_ts_shard_8_ts_idx |    true    |            3 | product_id               | ASC       |  false  |   true
      events     | events_crdb_internal_ts_shard_8_ts_idx |    true    |            4 | owner                    | ASC       |  false  |   true
      events     | events_crdb_internal_ts_shard_8_ts_idx |    true    |            5 | serial_number            | ASC       |  false  |   true
      events     | events_crdb_internal_ts_shard_8_ts_idx |    true    |            6 | event_id                 | ASC       |  false  |   true
    (11 rows)

    > SHOW COLUMNS FROM events;

            column_name        | data_type | is_nullable | column_default |       generation_expression       |                     indices                      | is_hidden
    ---------------------------+-----------+-------------+----------------+-----------------------------------+--------------------------------------------------+------------
      product_id               | INT8      |    false    | NULL           |                                   | {primary,events_crdb_internal_ts_shard_8_ts_idx} |   false
      owner                    | UUID      |    false    | NULL           |                                   | {primary,events_crdb_internal_ts_shard_8_ts_idx} |   false
      serial_number            | VARCHAR   |    false    | NULL           |                                   | {primary,events_crdb_internal_ts_shard_8_ts_idx} |   false
      event_id                 | UUID      |    false    | NULL           |                                   | {primary,events_crdb_internal_ts_shard_8_ts_idx} |   false
      ts                       | TIMESTAMP |    false    | NULL           |                                   | {primary,events_crdb_internal_ts_shard_8_ts_idx} |   false
      data                     | JSONB     |    true     | NULL           |                                   | {}                                               |   false
      crdb_internal_ts_shard_8 | INT4      |    false    | NULL           | mod(fnv32(CAST(ts AS STRING)), 8) | {events_crdb_internal_ts_shard_8_ts_idx}         |   true
    (7 rows)

## See also

- Indexes
- `SHOW INDEX`
- `DROP INDEX`
- `RENAME INDEX`
- `SHOW JOBS`
- Other SQL Statements
- Online Schema Changes