> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Secondary Indexes

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

An <InternalLink path="indexes">*index*</InternalLink> is a <InternalLink path="schema-design-overview#database-schema-objects">logical object</InternalLink> that helps CockroachDB queries find data more efficiently. When you create an index, CockroachDB creates a copy of the columns selected for the index, and then sorts the rows of data by indexed column values, without sorting the values in the table itself.

CockroachDB automatically creates an index on the table's <InternalLink path="primary-key">primary key</InternalLink> columns. This index is called the *primary index*. The primary index helps CockroachDB more efficiently scan rows, as sorted by the table's primary key columns, but it does not help find values as identified by any other columns.

*Secondary indexes* (i.e., all indexes that are not the primary index) improve the performance of queries that identify rows with columns that are not in a table's primary key. CockroachDB automatically creates secondary indexes for columns with a <InternalLink path="unique">`UNIQUE` constraint</InternalLink>.

This page provides best-practice guidance on creating secondary indexes, with a simple example based on Cockroach Labs's fictional vehicle-sharing company, <InternalLink path="movr">MovR</InternalLink>.

## Before you begin

Before reading this page, do the following:

* <InternalLink version="cockroachcloud" path="quickstart">Create a CockroachDB Standard cluster</InternalLink> or <InternalLink version="cockroachcloud" path="quickstart?filters=local">start a local cluster</InternalLink>.
* <InternalLink path="schema-design-overview">Review the database schema objects</InternalLink>.
* Review the [best practices](#best-practices).

## Create a secondary index

To add a secondary index to a table do one of the following:

* Add an `INDEX` clause to the end of a <InternalLink path="create-table#create-a-table-with-secondary-and-gin-indexes">`CREATE TABLE`</InternalLink> statement.

  `INDEX` clauses generally take the following form:

  ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  INDEX {index_name} ({column_names});
  ```

| Parameter         | Description                                                                                  |
| ----------------- | -------------------------------------------------------------------------------------------- |
| `{index\_name}`   | The name of the index.                                                                       |
| `{column\_names}` | The name of the column to index, or a comma-separated list of names of the columns to index. |

* Use a <InternalLink path="create-index">`CREATE INDEX`</InternalLink> statement.

  `CREATE INDEX` statements generally take the following form:

  ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  CREATE INDEX {index_name} ON {table_name} ({column_names});
  ```

| Parameter         | Description                                                                                  |
| ----------------- | -------------------------------------------------------------------------------------------- |
| `{index\_name}`   | The name of the index.                                                                       |
| `{table\_name}`   | The name of the table.                                                                       |
| `{column\_names}` | The name of the column to index, or a comma-separated list of names of the columns to index. |

For an example, see [Example](#example).

<Note>
  * If you do not specify a name for an index, CockroachDB will generate a name. - The notation for referring to an
    index is `{table_name}@{index_name}`.
</Note>

## Best practices

Here are some best practices for creating and using secondary indexes.

<Tip>
  The <InternalLink path="explain#success-responses">`EXPLAIN`</InternalLink> command provides index recommendations, including index actions
  and SQL statements to perform the actions.
</Tip>

### Index contents

* Index all columns that you plan to use for <InternalLink path="order-by">sorting</InternalLink> or <InternalLink path="select-clause#filter-rows">filtering</InternalLink> data.

  An index that stores all the columns needed by a query is also known as a *covering index* for that query. When a query has a covering index, CockroachDB can use that index directly instead of doing an "index join" with the primary index, which is likely to be slower.

  CockroachDB <InternalLink path="indexes#how-do-indexes-work">pushes filters</InternalLink> (i.e., values listed in a <InternalLink path="select-clause#parameters">`WHERE` clause</InternalLink>) into an index, which allows it to perform a finite number of sequential scans. In a `WHERE` clause with `n` constrained columns you can filter the first `n-1` columns either on a single constant value using the operator `=` or a list of constant values using the operator `IN`. You can filter column `n` against a range of values using any of the operators `!=`, `<`, `>`, or `NOT IN`.

  Columns with a higher cardinality (higher number of distinct values) should be placed in the index before columns with a lower cardinality. If the cardinality of the columns you wish to add to the index are similar, test multiple column arrangements in a non-production environment to determine the most performant arrangement.

* If you need to index the result of a function applied to one or more columns of a single table, use the function to create a <InternalLink path="computed-columns">computed column</InternalLink> and index the column.

* Avoid indexing on sequential keys (e.g., <InternalLink path="timestamp">`TIMESTAMP`/`TIMESTAMPTZ`</InternalLink> columns). Writes to indexes with sequential keys can result in <InternalLink path="understand-hotspots#hot-range">range hotspots</InternalLink> that negatively affect performance. Instead, use <InternalLink path="performance-best-practices-overview#unique-id-best-practices">randomly generated unique IDs</InternalLink> or <InternalLink path="performance-best-practices-overview#use-multi-column-primary-keys">multi-column keys</InternalLink>.

  If you are working with a table that **must** be indexed on sequential keys, use <InternalLink path="hash-sharded-indexes">hash-sharded indexes</InternalLink>. For details about the mechanics and performance improvements of hash-sharded indexes in CockroachDB, see our [Hash Sharded Indexes Unlock Linear Scaling for Sequential Workloads](https://www.cockroachlabs.com/blog/hash-sharded-indexes-unlock-linear-scaling-for-sequential-workloads) blog post.

* Use a <InternalLink path="create-index#parameters">`STORING` clause</InternalLink> to store columns of data that you want returned by common queries, but that you do not plan to use in query filters.

  The `STORING` clause specifies columns that are not part of the index key but should be stored in the index. If a column is specified in a query, and the column is neither indexed nor stored in an index, CockroachDB may either perform a full scan or perform an <InternalLink path="indexes#example">index join</InternalLink> if a suitable secondary index exists. However, if the optimizer determines that the index join is too expensive, then CockroachDB will perform a full table scan. For an example, see [Example](#example).

* Review the <InternalLink path="schema-design-overview#specialized-indexes">specialized indexes</InternalLink>, such as partial and inverted indexes, and decide if you need to create a specialized index instead of a standard index.

* Avoid creating secondary indexes that you do not need.
  * Queries can benefit from an index even if they only filter a prefix of its columns. For example, if you create an index of columns `(A, B, C)`, queries filtering `(A)` or `(A, B)` can use the index, so you don't need to also index `(A)`.
  * If you need to <InternalLink path="constraints#change-constraints">change a primary key</InternalLink>, and you do not plan to filter queries on the existing primary key column(s), do not use <InternalLink path="alter-table#alter-primary-key">`ALTER PRIMARY KEY`</InternalLink> because it creates a secondary index from an existing primary key. Instead, use <InternalLink path="alter-table#changing-primary-keys-with-add-constraint-primary-key">`DROP CONSTRAINT... PRIMARY KEY`/`ADD CONSTRAINT... PRIMARY KEY`</InternalLink>, which does not create a secondary index.

We **strongly recommend** adding size limits to all <InternalLink path="indexes">indexed columns</InternalLink>, which includes columns in <InternalLink path="primary-key">primary keys</InternalLink>.

Values exceeding 1 MiB can lead to <InternalLink path="architecture/storage-layer#write-amplification">storage layer write amplification</InternalLink> and cause significant performance degradation or even <InternalLink path="cluster-setup-troubleshooting#out-of-memory-oom-crash">crashes due to OOMs (out of memory errors)</InternalLink>.

To add a size limit using <InternalLink path="create-table">`CREATE TABLE`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE name (first STRING(100), last STRING(100));
```

To add a size limit using <InternalLink path="alter-table#alter-column">`ALTER TABLE... ALTER COLUMN`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE name ALTER first TYPE STRING(99);
```

### Index management

* Limit creation and deletion of secondary indexes to off-peak hours. Performance impacts are likely if done during peak business hours.

* Do not create indexes as the `root` user. Instead, create indexes as a <InternalLink path="schema-design-overview#control-access-to-objects">different user</InternalLink>, with fewer privileges, following <InternalLink path="security-reference/authorization#authorization-best-practices">authorization best practices</InternalLink>. This will likely be the same user that created the table to which the index belongs.

* Drop unused indexes whenever possible.
  * In the DB Console, visit the <InternalLink path="ui-databases-page">**Databases** page</InternalLink> and check databases and tables for <InternalLink path="ui-databases-page#index-recommendations">**Index Recommendations**</InternalLink> to drop unused indexes.
  * To understand usage statistics for an index, query the <InternalLink path="crdb-internal#index_usage_statistics">`crdb_internal.index_usage_statistics`</InternalLink> table.

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    SELECT * FROM crdb_internal.index_usage_statistics;
    ```

    To get more detailed information about the table and index names, run a join query against `crdb_internal.index_usage_statistics` and `crdb_internal.table_indexes`. For an example, see <InternalLink path="performance-recipes#fix-slow-writes">Fix slow writes</InternalLink>.

* Use a <InternalLink path="third-party-database-tools#schema-migration-tools">database schema migration tool</InternalLink> or the <InternalLink path="cockroach-sql">CockroachDB SQL client</InternalLink> instead of a <InternalLink path="third-party-database-tools#drivers">client library</InternalLink> to execute <InternalLink path="online-schema-changes">database schema changes</InternalLink>.

* Review the <InternalLink path="online-schema-changes#known-limitations">limitations of online schema changes</InternalLink>. CockroachDB <InternalLink path="online-schema-changes#schema-changes-within-transactions">doesn't guarantee the atomicity of schema changes within transactions with multiple statements</InternalLink>.

  Cockroach Labs recommends that you perform schema changes outside explicit transactions. When a database <InternalLink path="third-party-database-tools#schema-migration-tools">schema management tool</InternalLink> manages transactions on your behalf, include one schema change operation per transaction.

## Example

Suppose you want the MovR application to display all of the bikes available to the users of the MovR platform.

The `vehicles` table in MovR stores rows of data for each vehicle registered with MovR. Your application will need to read any data about vehicles into the application's persistence layer from this table. To display available bikes, the reads will need to filter on the `available` and `type` columns.

Open `max_init.sql`, and, under the `CREATE TABLE` statement for the `vehicles` table, add a `CREATE INDEX` statement for an index on the `type` and `available` columns of the `vehicles` table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE INDEX type_available_idx ON movr.vehicles (type, available);
```

This statement creates a secondary index named `type_available_idx`, on the `vehicles` table.

The MovR app might also need to display the vehicle's location and ID, but the app will not be filtering or sorting on those values. If any of the columns referenced in or returned by a query are not in a primary or secondary index key, CockroachDB will need to perform <InternalLink path="sql-tuning-with-explain#issue-full-table-scans">a full scan of the table</InternalLink> to find the value. Full table scans can be costly, and should be avoided whenever possible.

To help avoid unnecessary full table scans, add a `STORING` clause to the index:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE INDEX type_available_idx ON movr.vehicles (type, available) STORING (last_location);
```

The index will now store the values in `last_location`, which will improve the performance of reads from the `vehicles` table that return `type`, `available`, `id`, and `last_location` values and do not filter or sort on the `last_location` column.

The `max_init.sql` file should now look similar to the following:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE movr.max_schema.users (
    first_name STRING,
    last_name STRING,
    email STRING UNIQUE,
    CONSTRAINT "primary" PRIMARY KEY (first_name, last_name)
  );

CREATE TYPE movr.max_schema.vtype AS ENUM ('bike', 'scooter', 'skateboard');

CREATE TABLE movr.max_schema.vehicles (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
      type movr.max_schema.vtype,
      creation_time TIMESTAMPTZ DEFAULT now(),
      available BOOL,
      last_location STRING
  );

CREATE INDEX type_available_idx ON movr.max_schema.vehicles (type, available) STORING (last_location);

CREATE TABLE movr.max_schema.rides (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
      vehicle_id UUID REFERENCES movr.max_schema.vehicles(id),
      start_address STRING,
      end_address STRING,
      start_time TIMESTAMPTZ DEFAULT now(),
      end_time TIMESTAMPTZ
  );
```

To clear the database and re-initialize the schemas, first execute the statements in the `dbinit.sql` file as the `root` user:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql \
--certs-dir={certs-directory} \
--user=root \
-f dbinit.sql
```

Then, execute the statements in the `max_init.sql` and `abbey_init.sql` files:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql \
--certs-dir={certs-directory} \
--user=max \
--database=movr \
-f max_init.sql
```

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
-f abbey_init.sql
```

After the statements have been executed, you can see the new index in the <InternalLink path="cockroach-sql#sql-shell">CockroachDB SQL shell</InternalLink>.

Open the SQL shell to your cluster:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql \
--certs-dir={certs-directory} \
--user=max \
--database=movr
```

To view the indexes in the `vehicles` table, issue a <InternalLink path="show-index">`SHOW INDEXES`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW INDEXES FROM movr.max_schema.vehicles;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  table_name |     index_name     | non_unique | seq_in_index |  column_name  | direction | storing | implicit | visible
-------------+--------------------+------------+--------------+---------------+-----------+---------+----------+----------
  vehicles   | type_available_idx |     t      |            1 | type          | ASC       |    f    |    f     |    t
  vehicles   | type_available_idx |     t      |            2 | available     | ASC       |    f    |    f     |    t
  vehicles   | type_available_idx |     t      |            3 | last_location | N/A       |    t    |    f     |    t
  vehicles   | type_available_idx |     t      |            4 | id            | ASC       |    f    |    t     |    t
  vehicles   | vehicles_pkey      |     f      |            1 | id            | ASC       |    f    |    f     |    t
  vehicles   | vehicles_pkey      |     f      |            2 | type          | N/A       |    t    |    f     |    t
  vehicles   | vehicles_pkey      |     f      |            3 | creation_time | N/A       |    t    |    f     |    t
  vehicles   | vehicles_pkey      |     f      |            4 | available     | N/A       |    t    |    f     |    t
  vehicles   | vehicles_pkey      |     f      |            5 | last_location | N/A       |    t    |    f     |    t
(9 rows)
```

The output from this `SHOW` statement displays the names and columns of the two indexes on the table (i.e., `vehicles_pkey` and `type_available_idx`).

The `last_location` column's `storing` value is `true` in the `type_available_idx` index, and is therefore not sorted. The primary key column `id` is implicit in the index, meaning the `id` column is implicitly indexed in `type_available_idx`.

To see an index definition, use a <InternalLink path="show-create">`SHOW CREATE`</InternalLink> statement on the table that contains the index:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE TABLE movr.max_schema.vehicles;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
         table_name        |                                create_statement
---------------------------+---------------------------------------------------------------------------------
  movr.max_schema.vehicles | CREATE TABLE max_schema.vehicles (
                           |     id UUID NOT NULL DEFAULT gen_random_uuid(),
                           |     type max_schema.vtype NULL,
                           |     creation_time TIMESTAMPTZ NULL DEFAULT now():::TIMESTAMPTZ,
                           |     available BOOL NULL,
                           |     last_location STRING NULL,
                           |     CONSTRAINT vehicles_pkey PRIMARY KEY (id ASC),
                           |     INDEX type_available_idx (type ASC, available ASC) STORING (last_location)
                           | )
(1 row)
```

After creating a database, a user-defined schema, some tables, and secondary indexes, the database schema should be ready for your application to <InternalLink path="insert">write</InternalLink> and read data.

It's likely that you will need to update your database schema at some point. We also recommend reading about <InternalLink path="online-schema-changes">how online schema changes work in CockroachDB</InternalLink>.

## What's next?

* <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>
* <InternalLink path="insert">`INSERT`</InternalLink>

You might also be interested in the following pages:

* <InternalLink path="create-index">`CREATE INDEX`</InternalLink>
* <InternalLink path="indexes">Indexes</InternalLink>
* <InternalLink path="partial-indexes">Index a Subset of Rows with Partial Indexes</InternalLink>
* <InternalLink path="hash-sharded-indexes">Index Sequential Keys with Hash-Sharded Indexes</InternalLink>
* <InternalLink path="inverted-indexes">Index JSON and Array Data with Generalized Inverted Indexes</InternalLink>
* <InternalLink path="spatial-indexes">Index Spatial Data</InternalLink>
* <InternalLink path="cockroach-commands">Cockroach Commands</InternalLink>
* <InternalLink path="cockroach-commands">`cockroach` Commands Overview</InternalLink>
* <InternalLink path="schema-design-overview">Schema Design Overview</InternalLink>
* <InternalLink path="sql-name-resolution#naming-hierarchy">CockroachDB naming hierarchy</InternalLink>