Index match on a composite partition key column

This example uses the following table and indexes:

CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);
CREATE CUSTOM INDEX race_name_idx 
    ON cycling.rank_by_year_and_name (race_name) 
    USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX race_year_idx 
    ON cycling.rank_by_year_and_name (race_year) 
    USING 'StorageAttachedIndex';

Composite partition keys have a partition defined by multiple columns in a table. Normally, you would need to specify all the columns in the partition key to query the table with a WHERE clause. However, an SAI index makes it possible to define an index using a single column in the table’s composite partition key. You can create an SAI index on each column in the composite partition key, if you need to query based on just one column.

SAI indexes also allow you to query tables without using the inefficient ALLOW FILTERING directive. The ALLOW FILTERING directive requires scanning all the partitions in a table, leading to poor performance.

The race_year and race_name columns comprise the composite partition key for the cycling.rank_by_year_and_name table.

Query for a match on the column race_name:

  • Query

  • Result

SELECT * FROM cycling.rank_by_year_and_name 
    WHERE race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu';
 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |        Daniel MARTIN
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |      Benjamin PRADES
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 |          Adam PHELAN
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |         Thomas LEBAS

Query for a match on the column race_year:

  • Query

  • Result

SELECT * FROM cycling.rank_by_year_and_name 
    WHERE race_year = 2014;
 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    1 |    Phillippe GILBERT
      2014 |                        4th Tour of Beijing |    2 |        Daniel MARTIN
      2014 |                        4th Tour of Beijing |    3 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |        Daniel MARTIN
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com