what does `create index` do in cassandra tables?

Question

consider this example:

create table bite (
      id varchar PRIMARY KEY,
      feedid varchar,
      score bigint,
      data varchar
  );

create index bite_feedid on bite (feedid);
create index bite_score on bite (score);

I am not sure what the last two lines create index.. do? why is it important? Does it create a new table? If so, how can I look up using that?

Thanks

Answer 1

A secondary index creates a new table using the indexed column as primary key. Advantages of this approach is that your write/delete operations on a table will be automatically translated into multiples operations, you don't have to care about it. Now that Cassandra support logged batches it may not seem a big advantage but in Cassandra 0.7 ... 1.1 was a big stuff.

Secondary indexes should not be used when the query on the index will retrieve always one result (eg: putting secondary index on a uuid).

A good feature of s.i. is that you can both query a single column without knowing anything of the primary key and combine part of the primary key with a secondary index (using AND operator).

You can't perform WHERE clause with multiple secondary indexes combined in AND.

HTH, Carlo