Examine column index and query rules

Understand the columns on which you can define SAI indexes and run queries.

Let's walk through another example to understand the columns on which you can define SAI indexes and subsequently run queries that reference those columns.

While this guide's prior topic, SAI quick start, focused on defining multiple indexes based on non-primary key columns (a very useful feature), this topic expands the discussion to SAI indexes on each column in a composite partition key.

About composite partition keys

SAI makes it possible to define an index using a single column in the table's composite partition key. A composite partition key means that the partition is made up of multiple columns. In this case with an SAI index, you would specify just one of the columns that comprises the composite partition key, as shown in this topic. You could also define a separate SAI index that specifies another single column from the composite partition key.

Without an SAI index defined, in your query, the WHERE clause would need to specify all the partition key columns or use ALLOW FILTERING. This type of query can result in unpredictable performance. SAI solves this problem – see the examples below.

Create the keyspace and table, and insert simple data

First, create the demo2 keyspace:
CREATE KEYSPACE demo2 WITH replication = {'class': 'NetworkTopologyStrategy', 'Cassandra': '1'}; 
Define a database table where the composite partition key is made from id and age. We also define a name column cluster key:
CREATE TABLE demo2.person_id_name_compositkey2 (id int, age int, name text, primary key ((id, age), name));
Add some data:
INSERT INTO demo2.person_id_name_compositkey2 (id, name, age) VALUES (1, 'Rocco', 21); 
INSERT INTO demo2.person_id_name_compositkey2 (id, name, age) VALUES (2, 'John', 50); 
INSERT INTO demo2.person_id_name_compositkey2 (id, name, age) VALUES (3, 'Bryn', 33); 
INSERT INTO demo2.person_id_name_compositkey2 (id, name, age) VALUES (4, 'Jason', 34); 
Important: Notice that at this point, there is no SAI index defined.

Queries before SAI indexes are defined

Without an SAI index, a user cannot query on the partition key columns individually. Example:
SELECT * from demo2.person_id_name_compositkey2 where id > 0;
InvalidRequest: Error from server: code=2200 
[Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. 
If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

Define SAI indexes

Instead of using ALLOW FILTERING in your queries, now define SAI indexes. Examples:
CREATE CUSTOM INDEX ON demo2.person_id_name_compositkey2 (id) USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX ON demo2.person_id_name_compositkey2 (age) USING 'StorageAttachedIndex';

Run the example query again

With the SAI indexes defined, we can run the prior query example again, this time with desired results.
SELECT * from demo2.person_id_name_compositkey2 where id > 0;
 id | age | name
----+-----+-------
  3 |  33 |  Bryn
  1 |  21 |  Rocco
  2 |  50 |  John
  4 |  34 |  Jason
  
(4 rows)
(Try another query that specifies the composite partition's age column in the query.)

What about SAI and a partition key with only one column?

When there is only one column defining the partition key, there is no need for an index. SAI returns an error message. Example:
CREATE CUSTOM INDEX ON demo2.person_id_name_primarykey (id) USING 'StorageAttachedIndex';
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot create secondary index on the only partition key column id"