Now in private preview: Cluster Key Index support for Azure Cosmos DB Cassandra API
Join the private preview for cluster key index in Azure Cosmos DB Cassandra API. To enable on your account contact the Azure Cosmos DB Cassandra API team at email@example.com
Benefits of indexes on clustering keys
- Indexing cluster key helps improve performance even as data grows, unlike without index that degrades performance linearly as data grows.
- Faster queries with properly indexed cluster keys, throughput cost (RU consumption) is also reduced.
- Improved efficiency in aggregation and range query when filtering on an indexed cluster key or data with large partitions.
Cluster Key Index use cases
We will be creating a table, inserting sample data and running some sample queries to show how cluster key indexing works within Azure Cosmos DB Cassandra API.
CREATE TABLE uprofile.democluster(id int, name text, state text, level int, primary key ((id), name, state)); INSERT INTO uprofile.democluster(id, name, state, level) VALUES (103, 'Sara Nancy', 'active', 2);
- Filter on non-leading cluster key
SELECT * FROM uprofile.democluster WHERE state='active' AND name='Sara Nancy';
These types of queries are not allowed without index on name since the clause is not restricted without the preceding cluster key. With index on name, the query becomes valid, and the performance is also consistent.
- Filter on leading cluster key
SELECT * FROM uprofile.democluster where name='Sara Nancy';
Note that without index on name the query will require you to ALLOW FILTERING which will affect performance.
CREATE INDEX ON uprofile.democluster (name);
Indexing cluster key will help achieve consistent latency and RU/s consumption.
- Filter on cluster key while selecting with aggregation function
SELECT MIN(level) FROM uprofile.democluster WHERE name='Sara Nancy';
Without an index on ‘name’, this query requires ALLOW FILTERING and is inefficient.
SELECT MIN(level) FROM uprofile.democluster WHERE id=103 AND name='Sara Nancy';
Without a partition key query there is no need for ALLOW FILTERING, but the query is inefficient resulting in increased latency and costly throughput (RU) consumption.
Having an index on ‘name’, the aggregation query becomes efficient. You can index the partition key ‘id’, to achieve the best performance for your query and reduce RU.
If you’re interested in trying out cluster key index within Azure Cosmos DB Cassandra API, contact us at firstname.lastname@example.org for instructions to configure and setup your Cassandra API account. We are excited to have you try this new capability in our API for Cassandra in Azure Cosmos DB!
Stay up to date on the latest Azure Cosmos DB news and features by following us on Twitter @AzureCosmosDB.