incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Blake Eggleston <bdeggles...@gmail.com>
Subject Re: How to query secondary indexes
Date Wed, 28 Nov 2012 18:21:40 GMT
You're going to have a problem doing this in a single query because you're
asking cassandra to select a non-contiguous set of rows. Also, to my
knowledge, you can only use non equal operators on clustering keys. The
best solution I could come up with would be to define you table like so:

CREATE TABLE room_data (
room_id uuid,
in_room int,
temp float,
time timestamp,
PRIMARY KEY (room_id, in_room, temp));

Then run 2 queries:
SELECT * FROM room_data WHERE in_room > 7;
SELECT * FROM room_data WHERE temp > 50.0;

And do an intersection on the results.

I should add the disclaimer that I am relatively new to CQL, so there may
be a better way to do this.

Blake


On Wed, Nov 28, 2012 at 10:02 AM, Oren Karmi <okarmi@gmail.com> wrote:

> Hi,
>
> According to the documentation on Indexes (
> http://www.datastax.com/docs/1.1/ddl/indexes ),
> in order to use WHERE on a column which is not part of my key, I must
> define a secondary index on it. However, I can only use equality comparison
> on it but I wish to use other comparisons methods like greater than.
>
> Let's say I have a room with people and every timestamp, I measure
> the temperature of the room and number of people. I use the timestamp as my
> key and I want to select all timestamps where temperature was over 50
> degrees but I can't seem to be able to do it with a regular query even if I
> define that column as a secondary index.
> SELECT * FROM MyTable WHERE temp > 50.4571;
>
> My lame workaround is to define a secondary index on NumOfPeopleInRoom and
> than for a specific value
> SELECT * FROM MyTable WHERE NumOfPeopleInRoom = 7 AND temp > 50.4571;
>
> I'm pretty sure this is not the proper way for me to do this.
>
> How should I attack this? It feels like I'm missing a very basic concept.
> I'd appreciate it if your answers include also the option of not changing
> my schema.
>
> Thanks!!!
>

Mime
View raw message