incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jason Rutherglen <jason.rutherg...@gmail.com>
Subject Re: CQL: No indexed column error when < or <= in WHERE clause
Date Sun, 14 Aug 2011 14:09:55 GMT
This will be fully solved with CASSANDRA-2915 which will use Lucene as
a the secondary index type implementation.  Lucene has extremely fast
range queries built in.

On Sun, Aug 14, 2011 at 6:38 AM, Sal Fuentes <fuentesjr@gmail.com> wrote:
> The important piece that is mentioned in Jonathan's link is this:
> "One consequence of the KEYS index type being more like a hash index than a
> btree is shown here: even though birth_date is indexed, Cassandra couldn’t
> perform the range query “> 1970″ against it."
> hash index vs a btree index. Basically what this means is that these
> secondary indexes do not currently support queries of the type greater than,
> less than on a single column.
> However, Cassandra will allow you to submit these types of queries when you
> have multiple indexed columns; even though it may not be very efficient.
> "We can perform the range query now that the state column is also indexed,
> so Cassandra can use the state predicate as the primary and filter on the
> other with a nested loop."
> [default@demo] get users where birth_date = 1973;
> -------------------
> RowKey: prothfuss
> => (column=birth_date, value=1973, timestamp=1313327531411000)
> => (column=full_name, value=Patrick Rothfuss, timestamp=1313327526515000)
> 1 Row Returned.
> [default@demo] get users where birth_date > 1972;
> No indexed columns present in index clause with operator EQ
> [default@demo] get users where birth_date < 1974;
> No indexed columns present in index clause with operator EQ
> [default@demo] get users where birth_date >= 1973;
> No indexed columns present in index clause with operator EQ
>
> On Sun, Aug 14, 2011 at 6:11 AM, Martin von Zweigbergk
> <martin.von.zweigbergk@gmail.com> wrote:
>>
>> Hi Jens,
>>
>> I have never used CQL myself and I have barely used Cassandra, but I
>> think I've seen it mentioned before on this list that you need to use
>> compare for equality on at least one column (as indicated by "No
>> indexed columns present in by-columns clause with "equals" operator").
>> The lookup will then be done based on that column and additional
>> filtering (such as for "less than") will be done on the result of the
>> first lookup, which can potentially be a large data set. You might
>> also want to redesign your data model to allow for a more efficient
>> lookup.
>>
>> Hope that helps (despite my lack of knowledge on the subject)
>> Martin
>>
>> On Sun, Aug 14, 2011 at 8:53 AM, Jens Hartung <honis@gmx.de> wrote:
>> > I had indexed the number column in station column family. Do I also have
>> > to index another column?
>> >
>> > What I'm wondering, when I type "get station where number = 8210;" all
>> > works fine, but when I type "get station where number < 8210;" I'll get
>> > mentioned exception.
>> >
>> > Is there something, that I misunderstand?
>> >
>> > -------- Original-Nachricht --------
>> >> Datum: Sat, 13 Aug 2011 18:14:05 -0700
>> >> Von: Jonathan Ellis <jbellis@gmail.com>
>> >> An: user@cassandra.apache.org
>> >> Betreff: Re: CQL: No indexed column error when < or <= in WHERE clause
>> >
>> >> This is covered in
>> >>
>> >> http://www.datastax.com/dev/blog/whats-new-cassandra-07-secondary-indexes
>> >>
>> >> On Sat, Aug 13, 2011 at 2:49 PM, Jens Hartung <honis@gmx.de> wrote:
>> >> > Hi together,
>> >> >
>> >> > first, I'm using Cassandra Version 0.8.4 and access it via CQL 1.0.3.
>> >> >
>> >> > When I select data from Cassandra with = in WHERE clause, everything
>> >> works fine, but when using <= or < in WHERE clause, I always get
>> >> following
>> >> Exception:
>> >> >
>> >> > java.sql.SQLException: No indexed columns present in by-columns
>> >> > clause
>> >> with "equals" operator
>> >> >        at
>> >>
>> >> org.apache.cassandra.cql.jdbc.CassandraStatement.executeQuery(CassandraStatement.java:242)
>> >> >        at
>> >>
>> >> columnfamily.queries.CassandraQueries.singleColumnSelect(CassandraQueries.java:147)
>> >> > ...
>> >> >
>> >> > My select-statement: "SELECT number FROM station WHERE number <=
>> >> > 8210;"
>> >> >
>> >> > Output of describe keyspace (within cli):
>> >> > ColumnFamily: station
>> >> >      Key Validation Class: org.apache.cassandra.db.marshal.UTF8Type
>> >> >      Default column value validator:
>> >> org.apache.cassandra.db.marshal.UTF8Type
>> >> >      Columns sorted by: org.apache.cassandra.db.marshal.UTF8Type
>> >> >      Row cache size / save period in seconds: 0.0/0
>> >> >      Key cache size / save period in seconds: 200000.0/14400
>> >> >      Memtable thresholds: 0.2109375/1440/45 (millions of
>> >> ops/minutes/MB)
>> >> >      GC grace seconds: 864000
>> >> >      Compaction min/max thresholds: 4/32
>> >> >      Read repair chance: 1.0
>> >> >      Replicate on write: true
>> >> >      Built indexes: [station.station_number_idx]
>> >> >      Column Metadata:
>> >> >        [...]
>> >> >        Column Name: number
>> >> >          Validation Class: org.apache.cassandra.db.marshal.LongType
>> >> >          Index Name: station_number_idx
>> >> >          Index Type: KEYS
>> >> >        [...]
>> >> >
>> >> > Are the <, <=, >=, > operators not supported at this time?
>> >> >
>> >> > Greetings
>> >> > Jens
>> >> > --
>> >> > NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie!
>> >> > Jetzt informieren: http://www.gmx.net/de/go/freephone
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >> Jonathan Ellis
>> >> Project Chair, Apache Cassandra
>> >> co-founder of DataStax, the source for professional Cassandra support
>> >> http://www.datastax.com
>> >
>> > --
>> > NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie!
>> > Jetzt informieren: http://www.gmx.net/de/go/freephone
>> >
>
>
>
> --
> Salvador Fuentes Jr.
>

Mime
View raw message