Using a secondary index

Secondary Indexes

Create indexes on one or more columns after defining a table. Secondary indexes created with Storage-Attached Indexing (SAI) are used to query a table using a column other than the table’s partition key.

Do not add an SAI index and a DSE Search index on the same table. See CREATE CUSTOM INDEX for details.

Procedure

  • The table rank_by_year_and_name stores the rank of cyclists for races.

    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)
    );
  • Both race_year and race_name must be specified in a query because those columns comprise the partition key.

    SELECT *
    FROM cycling.rank_by_year_and_name
    WHERE race_year = 2014
      AND 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
    
    (3 rows)
  • A logical query to try is a listing of the rankings for a particular year. Because the table has a composite partition key with race_year and race_name, the following query fails because only the race_year column is specified in the WHERE clause.

    SELECT *
    FROM cycling.rank_by_year_and_name
    WHERE race_year = 2014;
    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"
  • However, this next example uses Storage-Attached Indexing (SAI) and the CREATE CUSTOM INDEX command. An SAI index is created for the race year, and the subsequent query succeeds.

    CREATE CUSTOM INDEX race_year_idx ON cycling.rank_by_year_and_name (race_year)
    USING 'StorageAttachedIndex';
    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
    
    (6 rows)

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