incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jens Hartung" <ho...@gmx.de>
Subject Re: CQL: No indexed column error when < or <= in WHERE clause
Date Sun, 14 Aug 2011 15:20:04 GMT
Thanks for all the explanations.

It's odd, that pure range queries are not supported at this time. But I think I'll find a
solution for my problem.

Greetings

-------- Original-Nachricht --------
> Datum: Sun, 14 Aug 2011 07:09:55 -0700
> Von: Jason Rutherglen <jason.rutherglen@gmail.com>
> An: user@cassandra.apache.org
> Betreff: Re: CQL: No indexed column error when < or <= in WHERE clause

> 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.
> >

-- 
Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de

Mime
View raw message