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.
If an index is created for the artist field, the hidden table structure is as follows.
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?
How Do I Use an Index?
- 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
- Query records by indexed column.
Figure 4 Querying the result
Each table can have multiple indexes, but the write performance may be affected.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.