Database Reference
In-Depth Information
Adding a secondary index
There is one exception to this rule, and that is when a secondary index is in use. A second-
ary index should be a familiar concept to anyone who has worked with a relational data-
base; in the relational world, they're often just referred to as indexes. Put simply, secondary
indexes allow us to perform a reasonably efficient lookup of rows using columns other than
the partition key.
In our case, we'd like to look up rows in
user_follows
by providing a value for the
follower_username
row but omitting a value for the
followed_username
parti-
tion key. To do this, we can use the
CREATE INDEX
statement:
CREATE INDEX ON "user_follows" ("follower_username");
The syntax here is pretty straightforward: we simply provide Cassandra with the name of a
table, and the name of a column within the table that we'd like to create an index on.
To see our index in action, let's add a few follow relationships to our new table:
INSERT INTO "user_follows"
("followed_username", "follower_username")
VALUES ('alice', 'bob');
INSERT INTO "user_follows"
("followed_username", "follower_username")
VALUES ('alice', 'carol');
INSERT INTO "user_follows"
("followed_username", "follower_username")
VALUES ('carol', 'bob');
INSERT INTO "user_follows"
("followed_username", "follower_username")
VALUES ('dave', 'bob');
Now let's say we'd like to answer the question, "Who does
bob
follow?" We can construct
a query of the same form that caused us an error before we created the index:
