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