CODEWITHSUNDEEP
cassandracassandra-2.0
Julien Fouilhé
Julien Fouilhé

Reputation: 2658

Query all rows with first item of compound partition key only

I have the following column family:

CREATE TABLE test."Data" (
    "ItemID" uuid,
    "DataID" uuid,
    PRIMARY KEY (("ItemID", "DataID"))
)

I want to get all the rows having "ItemSourceID" = someuuid.

Before, I had the following schema, and it was working great obviously:

CREATE TABLE test."Data" (
    "ItemID" uuid,
    "DataID" uuid,
    PRIMARY KEY (ItemID, "DataID")

But I had a lot of performance issues because there was too many rows for a specific ItemID (several millions).

I was wondering if the following requests would allow me to get all the results for a specific ItemID or if it is really not possible:

SELECT * FROM "Data" WHERE token("ItemID", "DataID") > token(e9e9ebfd-c9aa-11e4-b1a1-b8e85641b1e0, 00000000-0000-0000-0000-000000000000) LIMIT 1000;

And then replacing 00000000-0000-0000-0000-000000000000 by the last UUID I get until there's no result with this itemID anymore. Basic paginating.

I get results right now but I don't know if I'll get all of them since I'm not sure how the token works and if 00000000-0000-0000-0000-000000000000 is actually always the first.

I am using cassandra 2.1.4.

Thank you for your help

Upvotes: 0

Views: 162

Answers (1)

Jeff Jirsa
Jeff Jirsa

Reputation: 4426

Using ItemID as the partition key and DataID as the clustering key is likely the data model you want to consider. Benchmarks I've seen from last September indicate that having "millions" of cells in a partition SHOULD be OK in modern versions of Cassandra, as long as they're not tombstones (you can look for Patrick McFadin's talk at Cassandra Summit 2014 in SF from last September for details).

Iterating using a token would be a fallback, but I don't think you can guarantee that the UUID of all 0s is actually first - you'd want to SELECT the TOKEN LIMIT 1 to find the absolute lowest, and then use that resulting token in the subsequent SELECTS.

Personally, I'd probably try breaking up the partition manually - is the DataID time based (type 1 uuids?). If so, I'd probably try breaking the partition into logical date blocks (such as 1 partition per day).

Upvotes: 2

Related Questions