Create an index
Cassandra supports indexing any regular, non-primary key columns in a table. Any column designated as a partition key or clustering column cannot be indexed, unless DataStax Enterprise is the defined database.
If you wish to create a table query that uses anything other than the partition key to define which row or rows are to be retrieved, a column index must be created on each column in order to read the data.
Currently, these indexes can be created with CQL or GraphQL.
You can create an index using a mutation in /graphql-schema
.
In the following example, three indexes are created for the tables book
and reader
.
The table columns for these indexes are created are author
, birthdate
, and email
.
An index name can be defined, such as author_idx
in this example.
An additional option, indexType
can be defined to use SAI indexes if desired.
mutation createIndexes {
book: createIndex(
keyspaceName:"library",
tableName:"book",
columnName:"author",
indexName:"author_idx"
)
reader: createIndex(
keyspaceName:"library",
tableName:"reader",
columnName:"birthdate",
indexName:"reader_bdate_idx"
)
reader2: createIndex(
keyspaceName:"library",
tableName:"reader",
columnName:"email",
indexName:"reader_email_idx"
)
}
curl --location --request POST {base_url}/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createIndexes {\n book: createIndex(\n keyspaceName:\"library\",\n tableName:\"book\",\n columnName:\"author\",\n indexName:\"author_idx\"\n )\n reader: createIndex(\n keyspaceName:\"library\",\n tableName:\"reader\",\n columnName:\"birthdate\",\n indexName:\"reader_bdate_idx\"\n )\n reader2: createIndex(\n keyspaceName:\"library\",\n tableName:\"reader\",\n columnName:\"email\",\n indexName:\"reader_email_idx\"\n )\n}","variables":{}}'
Result TBD
Here is an additional example, which creates indexes that could be used in the REST API examples:
curl --location --request POST 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema' \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createIndexes {\n user1: createIndex(\n keyspaceName:\"'$KEYSPACE_NAME'\",\n tableName:\"'$TABLE_NAME'\",\n columnName:\"favorite_books\",\n indexName:\"fav_books_idx\",\n indexKind: VALUES\n )\n user2:createIndex(\n keyspaceName:\"'$KEYSPACE_NAME'\",\n tableName:\"'$TABLE_NAME'\",\n columnName:\"top_three_tv_shows\",\n indexName:\"tv_idx\",\n indexKind: VALUES\n )\n user3:createIndex(\n keyspaceName:\"'$KEYSPACE_NAME'\",\n tableName:\"'$TABLE_NAME'\",\n columnName:\"evaluations\",\n indexName:\"evalv_idx\",\n indexKind: VALUES\n )\n user4:createIndex(\n keyspaceName:\"'$KEYSPACE_NAME'\",\n tableName:\"'$TABLE_NAME'\",\n columnName:\"evaluations\",\n indexName:\"evalk_idx\",\n indexKind: KEYS\n )\n user5:createIndex(\n keyspaceName:\"'$KEYSPACE_NAME'\",\n tableName:\"'$TABLE_NAME'\",\n columnName:\"evaluations\",\n indexName:\"evale_idx\",\n indexKind: ENTRIES\n )\n users6: createIndex(\n keyspaceName:\"'$KEYSPACE_NAME'\",\n tableName:\"'$TABLE_NAME'\",\n columnName:\"current_country\",\n indexName:\"country_idx\"\n )\n}","variables":{}}'
CREATE INDEX books_idx ON users_keyspace.users (VALUES(favorite_books));
CREATE INDEX tv_idx ON users_keyspace.users (VALUES (top_three_tv_shows));
CREATE INDEX evalk_idx ON users_keyspace.users (KEYS (evaluations));
CREATE INDEX evalv_idx ON users_keyspace.users (VALUES (evaluations));
CREATE INDEX evale_idx ON users_keyspace.users (ENTRIES (evaluations));
CREATE INDEX country_idx ON users_keyspace.users (VALUES (current_country));
Result TBD
The CQL commands for creating these indexes is included here for reference.
The cqlsh
tool can be used to create the indexes if desired.