= CREATE INDEX
:description: Defines a new secondary index (2i) or storage-attached index (SAI) for a single column of a table.

{description}

{product} supports creating a secondary index or storage-attached index on most columns, including the partition and cluster columns of a `PRIMARY KEY`, collections, and static columns. 
For maps, you can index using the key, value, or entries (a key:value pair).

All column date types except the following are supported for SAI indexes:

* `counter`
* non-frozen user-defined type (UDT)

.One exception 
****
You cannot define an SAI index based on the partition key when it's comprised of only one column.
If you attempt to create an SAI index in this case, SAI issues an error message.
****

*See also:* xref:cassandra:reference/cql-commands/create-custom-index.adoc[CREATE CUSTOM INDEX] for Storage-Attached Indexes (SAI), xref:cassandra:reference/cql-commands/drop-index.adoc[DROP INDEX]

== Syntax

BNF definition:

[source,bnf]
----
include::example$BNF/index_name.bnf[]
----

// tag::syntax[]
----
CREATE INDEX [ IF NOT EXISTS ] <index_name>
  ON [<keyspace_name>.]<table_name> 
  ( [ ( KEYS | FULL | ENTRIES ) ] <column_name>)
    //   | [ (KEYS(<map_name>)) ]
    // | [ (VALUES(<map_name>)) ]
    // | [ (ENTRIES(<map_name>)) ]
   [USING 'sai']
  [ WITH OPTIONS = { <option_map> } ];
----
// end::syntax[]

.Syntax legend
[%collapsible]
====
include::cassandra:partial$cql-syntax-legend.adoc[]
====

== Required parameters

[cols="1,3"]  
|===

| Parameter | Description

| table_name
| Name of the table to index.

| column_name
| Name of the column to index.
SAI allows only alphanumeric characters and underscores in names.
SAI returns `InvalidRequestException` if you try to define an index on a column name that contains other characters, and does not create the index.
|===

== Optional parameters

[cols="1,3"]
|===

| Parameter | Description

| index_name
| Name of the index.
Enclose in quotes to use special characters or preserve capitalization.
If no name is specified, {product} names the index as `<table_name>\_<column_name>\_idx`.

include::cassandra:partial$index-naming.adoc[]

| keyspace_name
| Name of the keyspace that contains the table to index.
If no name is specified, the current keyspace is used.

| map_name
| Used with xref:cassandra:developing/cql/collections/collection-create.adoc[collections], identifier of the `map_name` specified in `CREATE TABLE` ...
`map(<map_name>)`.
The regular column syntax applies for collection types `list` and `set`.
|===

[cols=",,"]
|============================
|option_map 2+<|Define options in JSON simple format.
.4+||`case_sensitive` |Ignore case in matching string values. Default: `true`
|`normalize`|When set to `true`, perform https://unicode.org/faq/normalization.html[Unicode normalization] on indexed strings.
SAI supports Normalization Form C (NFC) Unicode.
When set to `true`, SAI normalizes the different versions of a given Unicode character to a single version, retaining all the marks and symbols in the index.
For example, SAI would change the character Å (U+212B) to Å (U+00C5).

When implementations keep strings in a normalized form, equivalent strings have a unique binary representation.
See https://unicode.org/reports/tr15/[Unicode Standard Annex #15, Unicode Normalization Forms].

Default: `false`.
|`ascii` |When set to `true`, SAI converts alphabetic, numeric, and symbolic characters that are not in the Basic Latin Unicode block (the first 127 ASCII characters) to the ASCII equivalent, if one exists.
For example, this option changes à to a.
Default: `false`.
|`similarity_function` |Vector search relies on computing the similarity or distance between vectors to identify relevant matches.
The similarity function is used to compute the similarity between two vectors.
Valid options are: `EUCLIDEAN`, `DOT_PRODUCT`, `COSINE`
Default: `COSINE`
|============================

== Usage notes

If the column already contains data, it is indexed during the execution of this statement.
After an index has been created, it is automatically updated when data in the column changes.

Indexing with the `CREATE INDEX` command can impact performance.
Before creating an index, be aware of when and xref:cassandra:developing/cql/indexing/2i/2i-when-to-use.adoc#when-no-index[when not to create an index].

*Restriction:* Indexing counter columns is not supported.

=== SAI indexes

You can define an SAI index on one of the columns in a table's composite partition key, i.e., a partition key comprised of multiple columns.
If you need to query based on one of those columns, an SAI index is a helpful option. 
In fact, you can define an SAI index on each column in a composite partition key, if needed.

Defining one or more SAI indexes based on any column in a database table allows queries to use the indexed column to filter results. 

=== SAI query operators

SAI supports the following query operators for tables with SAI indexes:

include::cassandra:partial$sai/supported-query-operators-list.adoc[]

SAI does not support the following query operators for tables with SAI indexes:

include::cassandra:partial$sai/notSupportedOperators.adoc[]

See the xref:cassandra:developing/cql/indexing/sai/sai-overview.adoc[SAI section].

== Examples

// LLP
// Need all of these for both 2i, SASI, and SAI:
// Create index on non-primary key column
// Create index on primary key column - partition key?
// on a composite partition key
// Create index on primary key column - clustering key
// set, list, map - key, value, entries, full

=== Creating a SAI index on a clustering column

Define a table having a xref:cassandra:reference/cql-commands/create-table.adoc#cqlPKcomposite[composite partition key], and then create an index on a clustering column.

[tabs]
====
CREATE TABLE::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/rank_by_year_and_name-table.cql[tag=compositepk]
----
--

CREATE INDEX::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/rank_by_year_and_name-table.cql[tag=createindex]
----
--

SELECT query::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/select_all_from_rank_by_year_and_name-rank.cql[]
----
--

SELECT result::
+
--
[source,language-cql]
----
include::cassandra:example$RESULTS/sai/select_all_from_rank_by_year_and_name-rank.result[]
----
--
====

=== Creating an index on a set or list collection

Create an index on a set or list collection column as you would any other column.
Enclose the name of the collection column in parentheses at the end of the `CREATE INDEX` statement.
For example, add a collection of teams to the `cyclist_career_teams` table to index the data in the teams set.

[tabs]
====
CREATE TABLE::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/cyclist_career_teams-table.cql[tag=setColumn]
----
--

CREATE INDEX::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/cyclist_career_teams-table.cql[tag=createidxset]
----
--

SELECT query::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/select_all_from_cyclist_career_teams-team.cql[]
----
--

SELECT result::
+
--
[source,language-cql]
----
include::cassandra:example$RESULTS/sai/select_all_from_cyclist_career_teams-team.result[]
----
--
====

[[CreatIdxCollKey]]
=== Creating an index on map keys

You can create an index on xref:cassandra:developing/cql/indexing/2i/_2i-create-on-collection.adoc[map collection keys].
If an index of the map values of the collection exists, drop that index before creating an index on the map collection keys.
Assume a cyclist table contains this map data where `nation` is the map key and `Canada` is the map value:

[source,no-highlight]
----
{'nation':'CANADA' }
----

To index map keys, use the `KEYS` keyword and map name in nested parentheses in the CREATE INDEX statement.
To run a `SELECT` query on the table, use xref:cassandra:developing/cql/dml.adoc#allow-filtering[CONTAINS KEY] in `WHERE` clauses.
This query returns cyclist teams that have an entry for the year 2015.

[tabs]
====
CREATE TABLE::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/cyclist_teams-table.cql[tag=mapColumn]
----
--

CREATE INDEX::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/cyclist_teams-table.cql[tag=keysidx]
----
--

SELECT query::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/cyclist_teams-table.cql[tag=queryindexkey]
----
--

SELECT result::
+
--
[source,language-cql]
----
include::cassandra:example$RESULTS/sai/cyclist_team-queries.result[]
----
--
====

=== Creating an index on map entries

You can create an index on map entries.
An `ENTRIES` index can be created only on a map column of a table that doesn't have an existing index.

To index collection entries, use the `ENTRIES` keyword and map name in nested parentheses.
To query the map entries in the table, use a `WHERE` clause with the map name and a value.
This query finds cyclists who are the same age.

[tabs]
====
CREATE TABLE::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/birthday_list_map_entries-table.cql[tag=blisttable]
----
--

CREATE INDEX::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/birthday_list_map_entries-table.cql[tag=entriesidx]
----
--

SELECT query::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/birthday_list_map_entries-table.cql[tag=ageentryquery]
----
--

SELECT result::
+
--
[source,language-cql]
----
include::cassandra:example$RESULTS/sai/select_from_birthday_list_where_age_23.result[]
----
--
====

Use the same index to find cyclists from the same country:

[tabs]
====
CREATE TABLE::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/birthday_list_map_entries-table.cql[tag=blisttable]
----
--

CREATE INDEX::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/birthday_list_map_entries-table.cql[tag=entriesidx]
----
--

SELECT query::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/birthday_list_map_entries-table.cql[tag=nationentryquery]
----
--

SELECT result::
+
--
[source,language-cql]
----
include::cassandra:example$RESULTS/sai/select_from_birthday_list_where_nation_netherlands.result[]
----
--
====

=== Creating an index on map values

To create an index on map values, use the `VALUES` keyword and map name in nested parentheses.
To query the table, use a `WHERE` clause with the map name and the value it contains.

[tabs]
====
CREATE TABLE::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/birthday_list_map_entries-table.cql[tag=blisttable]
----
--

CREATE INDEX::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/birthday_list_map_entries-table.cql[tag=mapvaluesidx]
----
--

SELECT query::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/birthday_list_map_entries-table.cql[tag=nationvaluesquery]
----
--

SELECT result::
+
--
[source,language-cql]
----
include::cassandra:example$RESULTS/sai/select_from_birthday_list_where_nation_netherlands_2.result[]
----
--
====

=== Creating an index on the full content of a frozen collection

You can create an index on a full `FROZEN` collection.
A `FULL` index can be created on a set, list, or map column of a table that doesn't have an existing index.

Create an index on the full content of a `FROZEN` `list`.
The table in this example stores the number of Pro wins, Grand Tour races, and Classic races that a cyclist has competed in.
To index collection entries, use the `FULL` keyword and collection name in nested parentheses.
For example, index the frozen list `rnumbers`.
To query the table, use a `WHERE` clause with the collection name and values.

[tabs]
====
CREATE TABLE::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/race_starts-table.cql[tag=frozenlist]
----
--

CREATE INDEX::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/race_starts-table.cql[tag=fullindex]
----
--

SELECT query::
+
--
[source,language-cql]
----
include::cassandra:example$CQL/sai/race_starts-table.cql[tag=selectrnumbers]
----
--

SELECT result::
+
--
[source,language-cql]
----
include::cassandra:example$RESULTS/sai/race_starts-queries.result[]
----
--
====
