Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (athena.apache.org: domain of jason.rutherglen@gmail.com
 designates 209.85.220.172 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CACFSpAkayUxGVyM1EvXbQktrHkcWdyCapYrMmLSw+VigyBrJUw@mail.gmail.com>
References: <20110803015822.204140@gmx.net>
	<1312339364.2893.10.camel@erebus.lan>
	<20110813214939.183200@gmx.net>
	<CALdd-zghj3OuwDR0P_yeAN_svHRj-+f67-TYimH5vg=RkBOprQ@mail.gmail.com>
	<20110814125313.326920@gmx.net>
	<CAOeW2eGrgJvQPivmE+bsX6iwJjqMQm80MfVAN=1+7NtPUb78Wg@mail.gmail.com>
	<CACFSpAkayUxGVyM1EvXbQktrHkcWdyCapYrMmLSw+VigyBrJUw@mail.gmail.com>
Date: Sun, 14 Aug 2011 07:09:55 -0700
Message-ID: 
 <CACn_-Ay90ap3b02_cTig_odhbUizJN090ThiPJdQjy9JOqtf2w@mail.gmail.com>
Subject: Re: CQL: No indexed column error when < or <= in WHERE clause
From: Jason Rutherglen <jason.rutherglen@gmail.com>
To: user@cassandra.apache.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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=
=E2=80=99t
> perform the range query =E2=80=9C> 1970=E2=80=B3 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 th=
an,
> less than on a single column.
> However, Cassandra will allow you to submit these types of queries when y=
ou
> 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 =3D 1973;
> -------------------
> RowKey: prothfuss
> =3D> (column=3Dbirth_date, value=3D1973, timestamp=3D1313327531411000)
> =3D> (column=3Dfull_name, value=3DPatrick Rothfuss, timestamp=3D131332752=
6515000)
> 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 >=3D 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 ha=
ve
>> > to index another column?
>> >
>> > What I'm wondering, when I type "get station where number =3D 8210;" a=
ll
>> > works fine, but when I type "get station where number < 8210;" I'll ge=
t
>> > 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 <=3D in WHERE cla=
use
>> >
>> >> This is covered in
>> >>
>> >> http://www.datastax.com/dev/blog/whats-new-cassandra-07-secondary-ind=
exes
>> >>
>> >> 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 =3D in WHERE clause, everyth=
ing
>> >> works fine, but when using <=3D or < in WHERE clause, I always get
>> >> following
>> >> Exception:
>> >> >
>> >> > java.sql.SQLException: No indexed columns present in by-columns
>> >> > clause
>> >> with "equals" operator
>> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at
>> >>
>> >> org.apache.cassandra.cql.jdbc.CassandraStatement.executeQuery(Cassand=
raStatement.java:242)
>> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at
>> >>
>> >> columnfamily.queries.CassandraQueries.singleColumnSelect(CassandraQue=
ries.java:147)
>> >> > ...
>> >> >
>> >> > My select-statement: "SELECT number FROM station WHERE number <=3D
>> >> > 8210;"
>> >> >
>> >> > Output of describe keyspace (within cli):
>> >> > ColumnFamily: station
>> >> > =C2=A0 =C2=A0 =C2=A0Key Validation Class: org.apache.cassandra.db.m=
arshal.UTF8Type
>> >> > =C2=A0 =C2=A0 =C2=A0Default column value validator:
>> >> org.apache.cassandra.db.marshal.UTF8Type
>> >> > =C2=A0 =C2=A0 =C2=A0Columns sorted by: org.apache.cassandra.db.mars=
hal.UTF8Type
>> >> > =C2=A0 =C2=A0 =C2=A0Row cache size / save period in seconds: 0.0/0
>> >> > =C2=A0 =C2=A0 =C2=A0Key cache size / save period in seconds: 200000=
.0/14400
>> >> > =C2=A0 =C2=A0 =C2=A0Memtable thresholds: 0.2109375/1440/45 (million=
s of
>> >> ops/minutes/MB)
>> >> > =C2=A0 =C2=A0 =C2=A0GC grace seconds: 864000
>> >> > =C2=A0 =C2=A0 =C2=A0Compaction min/max thresholds: 4/32
>> >> > =C2=A0 =C2=A0 =C2=A0Read repair chance: 1.0
>> >> > =C2=A0 =C2=A0 =C2=A0Replicate on write: true
>> >> > =C2=A0 =C2=A0 =C2=A0Built indexes: [station.station_number_idx]
>> >> > =C2=A0 =C2=A0 =C2=A0Column Metadata:
>> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0[...]
>> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0Column Name: number
>> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Validation Class: org.apache.cass=
andra.db.marshal.LongType
>> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Name: station_number_idx
>> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Type: KEYS
>> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0[...]
>> >> >
>> >> > Are the <, <=3D, >=3D, > operators not supported at this time?
>> >> >
>> >> > Greetings
>> >> > Jens
>> >> > --
>> >> > NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zur=C3=BCck-Garant=
ie!
>> >> > 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=C3=BCck-Garantie!
>> > Jetzt informieren: http://www.gmx.net/de/go/freephone
>> >
>
>
>
> --
> Salvador Fuentes Jr.
>