Skip to content

Latest commit

 

History

History
executable file
·
128 lines (88 loc) · 5.7 KB

create-index.md

File metadata and controls

executable file
·
128 lines (88 loc) · 5.7 KB
Raw
title summary toc
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.
false

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.

{{site.data.alerts.callout_info}}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.{{site.data.alerts.end}}

Required Privileges

The user must have the CREATE privilege on the table.

Synopsis

{% include sql/diagrams/create_index.html %}

Parameters

<style> table td:first-child { min-width: 225px; } </style>
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.
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.
index_name The name of the index to create, which must be unique to its table and follow these identifier rules.

If you don't 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 qualified_name of the table you want to create the index on.
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.

COVERING aliases STORING and works identically.

Examples

Create Indexes

To create the most efficient indexes, we recommend reviewing:

Single-Column Indexes

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

> CREATE INDEX ON products (price);

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 products (price, stock);

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 products (name, manufacturer_id);

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

> ALTER TABLE products ADD CONSTRAINT products_name_manufacturer_id_key UNIQUE (name, manufacturer_id);

Store Columns

Storing a column improves the performance of queries that retrieve (but don’t filter) its values.

> CREATE INDEX ON products (price) 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 price.

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 products (price DESC, stock);

How columns are sorted impacts the order of rows returned by queries using the index, which particularly affects queries using LIMIT.

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 products;
+----------+--------------------+--------+-----+--------+-----------+---------+
|  Table   |        Name        | Unique | Seq | Column | Direction | Storing |
+----------+--------------------+--------+-----+--------+-----------+---------+
| products | primary            | true   |   1 | id     | ASC       | false   |
| products | products_price_idx | false  |   1 | name   | N/A       | true    |
| products | products_price_idx | false  |   2 | price  | ASC       | false   |
+----------+--------------------+--------+-----+--------+-----------+---------+

> SELECT name FROM products@products_price_idx WHERE price > 10;

See Also