How Do I Use a Secondary Index?

Concept

In a GeminiDB Cassandra database, a primary key is the primary index, which can be used to query records. If you want to query records without the primary key, you can use secondary indexes.

Secondary Index Principles

A secondary index creates a hidden indexed table. The primary key becomes one of the columns in the hidden table.

Assume that there is a playlists table. The table structure is as follows:

CREATE TABLE playlists (
id int,
song_id int,
song_order int,
album text,
artist text,
title text,
PRIMARY KEY (id, song_id));

The query result is as follows.

Figure 1 Querying the result

If an index is created for the artist field, the hidden table structure is as follows.

Figure 2 Querying the result

artist is the primary key of the index table. id and song_id, functioning as the primary key of the original table, become common columns.

In Which Scenario Is the Index Not Recommended?

• Too many duplicate values exist in a column.

  For example, if a table contains 100 million records and the values of artist are the same, you are not advised to index the artist column.

• The counter column cannot be indexed.
• Columns that are frequently updated or deleted.

How Do I Use an Index?

1. Creating an index

   CREATE INDEX artist_names ON playlists( artist );

   Note: If the original table contains a large amount of data, indexed data needs to be rebuilt before queries.

   You can query the IndexInfo table to check whether the index is recreated. If the name of the created index exists, it indicates that the indexed data has been rebuilt.

   Figure 3 Querying the result
   
2. Query records by indexed column.
   Figure 4 Querying the result
   
   

   Each table can have multiple indexes, but the write performance may be affected.