CREATE INDEX

On this page

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.

Required privileges

The user must have the CREATE privilege on the table.

Synopsis

Standard index:

Inverted index:

Parameters

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)

See also

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

Was this helpful?