cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From aaron morton <>
Subject Re: Indexes on heterogeneous rows
Date Thu, 14 Apr 2011 20:26:09 GMT
> (This is a case were 1/3 of the rows are of type 2, but, say only a few hundred rows of
type 2 have e=5.)

How many rows would have e=5 without worrying about their type value?

On 14 Apr 2011, at 23:48, David Boxenhorn wrote:

> Thanks. I'm aware that I can roll my own. I wanted to avoid that, for ease of use, but
especially for atomicity concerns. 
> I thought that the secondary index would bring into memory all keys where type=2, and
then iterate over them to find keys where=5. (This is a case were 1/3 of the rows are of type
2, but, say only a few hundred rows of type 2 have e=5.) The reason why I put "type" first
is that queries on type will always be an exact match, whereas the other clauses might be
> On Thu, Apr 14, 2011 at 2:07 PM, aaron morton <> wrote:
> You could make your own inverted index by using keys like  "e=5-type=2" where the columns
are either the keys for the object or the objects themselves. Then just grab the full row
back. If you know you always want to run queries like that. 
> This recent discussion and blog post from Ed is good background
> I'm not sure how efficient the join from "e" to type would be. AFAIK it will iterate
all keys where e=5 and lookup corresponding rows to find out if type = 2. 
> If know how you want to read things back and need to deal with lots-o-data I would start
testing with custom indexes. Then compare to the built in ones, it should be reasonably simple
add them for a test.   
> Hope that helps. 
> Aaron
> On 14 Apr 2011, at 22:33, David Boxenhorn wrote:
>> Thank you for your answer, and sorry about the sloppy terminology.
>> I'm thinking of the scenario where there are a small number of results in the result
set, but there are billions of rows in the first of your secondary indexes.
>> That is, I want to do something like (not sure of the CQL syntax):
>> select * where type=2 and e=5
>> where there are billions of rows of type 2, but some manageable number of those rows
have e=5.
>> As I understand it, secondary indexes are like column families, where each value
is a column. So the billions of rows where type=2 would go into a single row of the secondary
index. This sounds like a problem to me, is it?  
>> I'm assuming that the billions of rows that don't have column "e" at all (those rows
of other types) are not a problem at all...
>> On Thu, Apr 14, 2011 at 12:12 PM, aaron morton <> wrote:
>> Need to clear up some terminology here. 
>> Rows have a key and can be retrieved by key. This is *sort of* the primary index,
but not primary in the normal RDBMS sense. 
>> Rows can have different columns and the column names are sorted and can be efficiently
>> There are "secondary indexes" in cassandra 0.7 based on column values
>> So you could create secondary indexes on the a,e, and h columns and get rows that
have specific values. There are some limitations to secondary indexes, read the linked article.

>> Or you can make your own secondary indexes using row keys as the index values.
>> If you have billions of rows, how many do you need to read back at once?
>> Hope that helps
>> Aaron
>> On 14 Apr 2011, at 04:23, David Boxenhorn wrote:
>>> Is it possible in 0.7.x to have indexes on heterogeneous rows, which have different
sets of columns?
>>> For example, let's say you have three types of objects (1, 2, 3) which each had
three members. If your rows had the following pattern
>>> type=1 a=? b=? c=?
>>> type=2 d=? e=? f=?
>>> type=3 g=? h=? i=?
>>> could you index "type" as your primary index, and also index "a", "e", "h" as
secondary indexes, to get the objects of that type that you are looking for?
>>> Would it work if you had billions of rows of each type?

View raw message