Untitled :: DataStax Astra DB Serverless Documentation

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.

graphQL mutation
cURL command
Result

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 command
CQL command
Result

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.