3

I'm storing linked words in a Cassandra v2 cluster and I'm having a hard time doing efficient reads. I'm trying to fetch the top X rows with the highest counter value.

  1. Cassandra doesn't store the rows in any ordered way, so simply adding LIMIT X to the CQL query returns random-ish rows.
  2. The ORDER BY command in CQL only works for columns that are (part of) the key.
  3. OrderPreservingPartitioner doesn't exist anymore in Cassandra v2.

At the moment, when doing a read, I have to fetch all rows and manually select those X rows with the highest counter value. This literally slows some queries down by 100x, because even mildly popular words often have hundreds of thousands of words linked to them, not to mention the waste of bandwidth and other resources.

Are there any clever techniques to make this more efficient?

  • 2
    Store the data in an RDBMS? – Max Vernon Sep 17 '14 at 20:13
  • 2
    @MaxVernon What's the point of storing relational data in a relational database system? – Kermit Sep 17 '14 at 20:15
  • @MaxVernon but ... i thought JOINs were slow! – swasheck Sep 17 '14 at 20:17
  • I know, its a really radical choice. – Max Vernon Sep 17 '14 at 20:17
  • How unprofessional... – natli Sep 18 '14 at 7:38
1

I'll try to address your concerns one at a time.

  1. Cassandra doesn't store the rows in any ordered way, so simply adding LIMIT X to the CQL query returns random-ish rows.

  2. The ORDER BY command in CQL only works for columns that are (part of) the key.

Not entirely correct, on either point. The on-disk sort order in Cassandra is determined by the first clustering column of your primary key. As a result, ORDER BY will also only work on that column (and only that column).

I'm trying to fetch the top X rows with the highest counter value.

Cassandra will not allow you to put a secondary index on a counter column, nor can you make it a part of your key (which also means you cannot sort by it). Therefore, querying data by the value of a counter column is not possible.

It would appear that your use case is not an appropriate fit for Cassandra. I would suggest solving this issue with a relational database or something else that would provide you with the necessary aggregation tools.

  • Thanks for clarifying the random row thing. I'm aware of these Cassandra restrictions, but I'm not entirely sure Cassandra isn't right for my use case. I was thinking about tracking a separate counter for each word. Each time it reaches 10.000 or so, I could re-order the rows manually (after adding a position column to the table.) But I'd have to constantly stream this new table's content to check for 10k. Would this be so inefficiënt another database system would outperform Cassandra? Because so far, no other database system has been able to keep up with the amount of data I'm throwing at it. – natli Sep 18 '14 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.