Search code examples
cassandracqldatabasenosql

Is a read with one secondary index faster than a read with multiple in cassandra?

I have this structure that I want a user to see the other user's feeds. One way of doing it is to fan out an action to all interested parties's feed.

That would result in a query like select from feeds where userid=

otherwise i could avoid writing so much data and since i am already doing a read I could do:

select from feeds where userid IN (list of friends).

is the second one slower? I don't have the application yet to test this with a lot of data/clustering. As the application is big writing code to test a single node is not worth it so I ask for your knowledge.

Solution

  • If your title is correct, and userid is a secondary index, then running a SELECT/WHERE/IN is not even possible. The WHERE/IN clause only works with primary key values. When you use it on a column with a secondary index, you will see something like this:

    Bad Request: IN predicates on non-primary-key columns (columnName) is not yet supported

    Also, the DataStax CQL3 documentation for SELECT has a section worth reading about using IN:

    When not to use IN

    The recommendations about when not to use an index apply to using IN in the WHERE clause. Under most conditions, using IN in the WHERE clause is not recommended. Using IN can degrade performance because usually many nodes must be queried. For example, in a single, local data center cluster with 30 nodes, a replication factor of 3, and a consistency level of LOCAL_QUORUM, a single key query goes out to two nodes, but if the query uses the IN condition, the number of nodes being queried are most likely even higher, up to 20 nodes depending on where the keys fall in the token range.

    As for your first query, it's hard to speculate about performance without knowing about the cardinality of userid in the feeds table. If userid is unique or has a very high number of possible values, then that query will not perform well. On the other hand, if each userid can have several "feeds," then it might do ok.

    Remember, Cassandra data modeling is about building your data structures for the expected queries. Sometimes, if you have 3 different queries for the same data, the best plan may be to store that same, redundant data in 3 different tables. And that's ok to do.

    I would tackle this problem by writing a table geared toward that specific query. Based on what you have mentioned, I would build it like this:

    CREATE TABLE feedsByUserId
    userid UUID,
    feedid UUID,
    action text,
    PRIMARY KEY (userid, feedid));

    With a composite primary key made up of userid as the partitioning key you will then be able to run your SELECT/WHERE/IN query mentioned above, and achieve the expected results. Of course, I am assuming that the addition of feedid will make the entire key unique. if that is not the case, then you may need to add an additional field to the PRIMARY KEY. My example is also assuming that userid and feedid are version-4 UUIDs. If that is not the case, adjust their types accordingly.