25

I'm undecided whether it's better, performance-wise, to use a very commonly shared column value (like Country) as partition key for a compound primary key or a rather unique column value (like Last_Name).

Looking at Cassandra 1.2's documentation about indexes I get this:

"When to use an index: Cassandra's built-in indexes are best on a table having many rows that contain the indexed value. The more unique values that exist in a particular column, the more overhead you will have, on average, to query and maintain the index. For example, suppose you had a user table with a billion users and wanted to look up users by the state they lived in. Many users will share the same column value for state (such as CA, NY, TX, etc.). This would be a good candidate for an index."

"When not to use an index: Do not use an index to query a huge volume of records for a small number of results. For example, if you create an index on a column that has many distinct values, a query between the fields will incur many seeks for very few results. In the table with a billion users, looking up users by their email address (a value that is typically unique for each user) instead of by their state, is likely to be very inefficient. It would probably be more efficient to manually maintain the table as a form of an index instead of using the Cassandra built-in index. For columns containing unique data, it is sometimes fine performance-wise to use an index for convenience, as long as the query volume to the table having an indexed column is moderate and not under constant load."

Looking at the examples from CQL's SELECT for

"Querying compound primary keys and sorting results", I see something like a UUID being used as partition key... which would indicate that it's preferable to use something rather unique?

enter image description here

Charles
  • 48,924
  • 13
  • 96
  • 136
andandandand
  • 20,448
  • 55
  • 163
  • 248

3 Answers3

42

Indexing in the documentation you wrote up refers to secondary indexes. In cassandra there is a difference between the primary and secondary indexes. For a secondary index it would indeed be bad to have very unique values, however for the components in a primary key this depends on what component we are focusing on. In the primary key we have these components:

PRIMARY KEY(partitioning key, clustering key_1 ... clustering key_n)

The partitioning key is used to distribute data across different nodes, and if you want your nodes to be balanced (i.e. well distributed data across each node) then you want your partitioning key to be as random as possible. That is why the example you have uses UUIDs.

The clustering key is used for ordering so that querying columns with a particular clustering key can be more efficient. That is where you want your values to not be unique and where there would be a performance hit if unique rows were frequent.

The cql docs have a good explanation of what is going on.

Lyuben Todorov
  • 15,392
  • 6
  • 46
  • 67
  • Does this mean, "partitioning key" is one row, and "clustering key_1" and so on, does the value ordering inside the same row? Thanks for your answer, this is the answer I landed after searching for long the correlation between the Partitioner, Partition Key and Compound Primary Key. From what I see, it is the Partition Key that messes up the distribution among a cluster, and if that is random, the rest of the Compound Key, i.e. Clustering key_1 and so on, CLUSTERING KEYS DO NOT AFFECT THE DISTRIBUTION AMONG NODES. – Ravindranath Akila Feb 01 '14 at 05:24
  • 2
    @RavindranathAkila The clustering key affects how columns are aligned (ordered) in a physical node, but you are right that the distribution amongst nodes depends solely on the partitioning key. – Lyuben Todorov Feb 01 '14 at 06:14
  • Thanks Lyuben! This helps a lot! – Ravindranath Akila Feb 02 '14 at 05:16
  • It depends on what query you need on a business case to decide which should be the partition key. Saying partitionkey should be random as possible not always the best case. Because if the partitions are too much distributed on nodes, and you have a query to get data from different partitions, it also affects the performance. – Barış Velioğlu Jan 03 '19 at 11:41
  • The link is broken. Updated link here? https://docs.datastax.com/en/archived/cql/3.3/cql/cql_using/useCompositePartitionKeyConcept.html – IceTea Jul 26 '19 at 05:22
8

if you use cql3, given a column family:

CREATE TABLE table1 (
  a1 text,
  a2 text,
  b1 text,
  b2 text,
  c1 text,
  c2 text,
  PRIMARY KEY ( (a1, a2), b1, b2) )
);

by defining a primary key ( (a1, a2, ...), b1, b2, ... )

This implies that:

a1, a2, ... are fields used to craft a row key in order to:

  • determine how the data is partitioned
  • determine what is phisically stored in a single row
  • referred as row key or partition key

b1, b2, ... are column family fields used to cluster a row key in order to:

  • create logical sets inside a single row
  • allow more flexible search schemes such as range range
  • referred as column key or cluster key

All the remaining fields are effectively multiplexed / duplicated for every possible combination of column keys. Here below an example about composite keys with partition keys and clustering keys work.

If you want to use range queries, you can use secondary indexes or (starting from cql3) you can declare those fields as clustering keys. In terms of speed having them as clustering key will create a single wide row. This has impact on speed since you will fetch multiple clustering key values such as:

select * from accounts where Country>'Italy' and Country<'Spain'

natbusa
  • 1,480
  • 1
  • 17
  • 24
  • I'm wondering about the performance when using clustering keys and range queries. The documentation is pretty vocal about only using secondary indices for equality comparisons as range comparisons will have Cassandra iterating over results to compare (due to KEYS indexing). How are range queries with clustering keys (i.e. the primary key parts not making up the row/partition key), do you know? – DanielSmedegaardBuus Dec 05 '13 at 13:35
  • 2
    Secondary indices effectively create a binary tree of hash row keys partitioned by the selected column. While column keys are meant for indexing the the columns within a row. Secondary indices should be used only if the cardinality of the column values is low (e.g. countries or postcodes) – natbusa Dec 08 '13 at 21:48
  • PRIMARY KEY ( (a1), b1, b2) ) is the same as PRIMARY KEY ( a1, b1, b2 ) – natbusa Dec 08 '13 at 21:49
  • column keys in compound/composite primary keys can be sped up by using the clustering syntax, for example: WITH CLUSTERING ORDER BY (b1 DESC); in this case descending queries will then be faster than ascending ones. This can be quite useful if you are interested in the last column value (typically used with timestamps typed columns) – natbusa Dec 08 '13 at 21:55
  • Thank you @natalinobusa, for clarifying the binary tree nature of the secondary index. As I understand you, range queries on the column keys is then similarly performant to range queries on the primary keys? Thanks again :) – DanielSmedegaardBuus Dec 09 '13 at 09:52
  • You can use range queries on the b1, b2, etc fields on the above example. Another catch, if you provide multiple clauses in the where statement you can only provide a range to the last column index, and remember that the order of indices does matter. Example: **select * from table1 where a1=c1 and a2=c2 and b1=c3 and b2 >c4** where c1, c2, c3,c4 are some constants of your choice – natbusa Dec 10 '13 at 10:35
  • Yeah, I just found that out the hard way :D LOL! Thank you for the help :) – DanielSmedegaardBuus Dec 10 '13 at 16:03
1

I am sure you would have got the answer but still this can help you for better understanding.

CREATE TABLE table1 (
  a1 text,
  a2 text,
  b1 text,
  b2 text,
  c1 text,
  c2 text,
  PRIMARY KEY ( (a1, a2), b1, b2) )
);

here the partition keys are (a1, a2) and row keys are b1,b2.

combination of both partition keys and row keys must be unique for each new record entry.

the above primary key can be define like this.

Node< key, value>

Node<(a1a2), Map< b1b2, otherColumnValues>>

as we know Partition Key is responsible for data distribution accross your nodes.

So if you are inserting 100 records in table1 with same partition keys and different row keys. it will store data in same node but in different columns.

logically we can represent like this.

Node<(a1a2), Map< string1, otherColumnValues>, Map< string2, otherColumnValues> .... Map< string100, otherColumnValues>>

So the record will store sequentially in memory.

Aftab
  • 766
  • 1
  • 7
  • 19