It's my understanding that if cardinality of the first part of the primary key has low cardinality, you will struggle with cluster balance as (unless you use WITH COMPACT STORAGE) the first entry of the primary key equates to the row key from the traditional interface, thus all entries related to a single value for the "counter" column will map to the same partition.
So consider the cardinality of this field, if cardinality is low, you might need to remodel with PRIMARY KEY (counter, ts, key1) then tack on WITH COMPACT STORAGE (then the entire primary key becomes the row key, but you can only have one column which is not part of the primary key) If cardinality of "counter" is high, then you have nothing to worry about. On Wed, Jun 19, 2013 at 3:16 PM, Francisco Andrades Grassi < bigjoc...@gmail.com> wrote: > Hi, > > I believe what he's recommending is: > > CREATE TABLE count3 ( > counter text, > ts timeuuid, > key1 text, > value int, > PRIMARY KEY (counter, ts) > ) > > That way *counter* will be your partitioning key, and all the rows that > have the same *counter* value will be clustered (stored as a single wide > row sorted by the *ts* value). In this scenario the query: > > where counter = 'test' and ts > minTimeuuid('2013-06-18 16:23:00') and ts > < minTimeuuid('2013-06-18 16:24:00'); > > would actually be a sequential read on a wide row on a single node. > > -- > Francisco Andrades Grassi > www.bigjocker.com > @bigjocker > > On Jun 19, 2013, at 12:17 PM, "Ryan, Brent" <br...@cvent.com> wrote: > > Tyler, > > You're recommending this schema instead, correct? > > CREATE TABLE count3 ( > counter text, > ts timeuuid, > key1 text, > value int, > PRIMARY KEY (ts, counter) > ) > > I believe I tried this as well and ran into similar problems but I'll > try it again. I'm using the "ByteOrderedPartitioner" if that helps with > the latest version of DSE community edition which I believe is Cassandra > 1.2.3. > > > Thanks, > Brent > > > From: Tyler Hobbs <ty...@datastax.com> > Reply-To: "user@cassandra.apache.org" <user@cassandra.apache.org> > Date: Wednesday, June 19, 2013 11:00 AM > To: "user@cassandra.apache.org" <user@cassandra.apache.org> > Subject: Re: timeuuid and cql3 query > > > On Wed, Jun 19, 2013 at 8:08 AM, Ryan, Brent <br...@cvent.com> wrote: > >> >> CREATE TABLE count3 ( >> counter text, >> ts timeuuid, >> key1 text, >> value int, >> PRIMARY KEY ((counter, ts)) >> ) >> > > Instead of doing a composite partition key, remove a set of parens and let > ts be your clustering key. That will cause cql rows to be stored in sorted > order by the ts column (for a given value of "counter") and allow you to do > the kind of query you're looking for. > > > -- > Tyler Hobbs > DataStax <http://datastax.com/> > > >