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.zweigbe...@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 <ho...@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 <jbel...@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 <ho...@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.
