0

I am following "CQL3 for Cassandra experts" (http://www.datastax.com/dev/blog/cql3-for-cassandra-experts#comment-259613) article. Executing 

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

works fine. But when trying to run

CREATE INDEX ON playlists(artist);

I get an error:

An error occurred when executing the SQL command:
CREATE INDEX ON playlists(artist)

Cannot create index on PRIMARY KEY part artist
‘CREATE INDEX ON playlists(artist)’

The article says that it should work fine. Is it a bug or am I doing something wrong?

I am running Cassandra 1.2.4 and connecting to it using JDBC 1.2.5 driver.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Leon
  • 41
  • 4

2 Answers2

0

You can not create index on partitions key i.e any column part of composite key, as they are auto indexed. By the way why do you need to create an index on part of a primary key?

abhi
  • 4,762
  • 4
  • 29
  • 49
  • From "CQL3 for Cassandra Experts": "To illustrate this last, consider the query SELECT * FROM playlists WHERE artist = 'Fu Manchu'. With the schema as given so far, this requires a sequential scan across the entire playlists dataset. But if we first CREATE INDEX ON playlists(artist), Cassandra can efficiently pull out the records in question." – Leon Jul 09 '13 at 18:41
  • not sure about the fact. Seems like some one has also asked the same thing, see the 9th comment and unfortunately still not cleared. – abhi Jul 09 '13 at 18:54
  • After few minutes googling, now i can say that it is some what misinterpreted from their side. See the cassandra post https://groups.google.com/forum/#!topic/nosql-databases/8qJmR46gf5I – abhi Jul 09 '13 at 19:08
  • If so it is unfortunate. If I understand correctly in this example id is a partitioning key while other components of the composite keys are used for clustering of data on a disk. The partitioning key defines Cassandra's node where data will be stored. All query examples I see are like `SELECT song_id FROM playlists WHERE id = '62c36092-82a1-...' AND artist < 'ZZ Top'`. This will only search on one node while I need to search across the entire Cassandra cluster. I need to effectively run a query like `SELECT song_id FROM playlists WHERE artist < 'ZZ Top'`. – Leon Jul 10 '13 at 19:38
0

For the sake of future arrivers, apparently this was fixed in later versions of Cassandra. I'm have the same tables in versions 2.0.8.39 and 1.2.13.2 and creating the index does work and the former, but not on the latter.

idoda
  • 6,248
  • 10
  • 39
  • 52