Using an index

Using CQL to create an index on a column after defining a table.

Using CQL, you can create an index on a column after defining a table. In Apache Cassandra™ 2.1 and later, you can index a collection column. The music service example shows how to create an index on the artists column of playlist, and then query Cassandra for songs by a particular artist:

CREATE INDEX artist_names ON playlists( artist );

An index name is optional. If you do not provide a name, Cassandra assigns a name such as artist_idx. If you provide a name, such as artist_names, the name must be unique within the keyspace. After creating an index for the artist column and inserting values into the playlists table, greater efficiency is achieved when you query Cassandra directly for artist by name, such as Fu Manchu:

SELECT * FROM playlists WHERE artist = 'Fu Manchu';

As mentioned earlier, when looking for a row in a large partition, narrow the search. This query, although a contrived example using so little data, narrows the search to a single id.

SELECT * FROM playlists WHERE id = 62c36092-82a1-3a00-93d1-46196ee77204 AND artist = 'Fu Manchu';

The output is: