You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by "Petter von Dolwitz (Hem)" <pe...@gmail.com> on 2013/10/24 14:38:51 UTC

Problems using secondary index with IN keyword

Hi,

I have a table that (in simplified version) looks like this:

CREATE TABLE mytable (
  a varchar,
  b varchar,
  c varchar
  d timstamp,
  e varchar,
  PRIMARY KEY (a, b, c, d)
);

CREATE INDEX mytable_c_idx ON mytable ( c );

After populating I execute:

SELECT * FROM mytable WHERE c='myvalue';

which works fine. However, using:

SELECT * FROM mytable WHERE c IN ('myvalue');

gives me:
Bad Request: PRIMARY KEY part c cannot be restricted (preceding part b is
either not restricted or by a non-EQ relation)

Can anybody explain this? My aim is to query for more than one value in the
c column. Is this supported?

Thanks,
Petter

Re: Problems using secondary index with IN keyword

Posted by Robert Coli <rc...@eventbrite.com>.
Before CASSANDRA-1337 [1] (Cassandra 2.1 line) secondary indexes + vnodes
can have very slow performance due to the potentially large number of
splits/nodes sequentially scanned.

=Rob
[1] https://issues.apache.org/jira/browse/CASSANDRA-1337

Re: Problems using secondary index with IN keyword

Posted by Jan Algermissen <ja...@nordsc.com>.
On 24.10.2013, at 22:00, Petter von Dolwitz (Hem) <pe...@gmail.com> wrote:

> I have defined the secondary index on a field that is part of the primary key. This should be ok. Maybe you missed the CREATE INDEX bit in my original post.

Yes I did - despite actually looking for it :-) Sorry.

Then I am curious, too what the intended behaviour is.

Jan




> I might end up not using secondary indexes but since the feature is there and I need the functionality I would like to know its limitations and if the behaviour I am experiencing is a problem with my design, a problem with the data or a problem with Cassandra.
> 
> /Petter
> 
> Den torsdagen den 24:e oktober 2013 skrev Jan Algermissen:
> 
> On 24.10.2013, at 15:13, Petter von Dolwitz (Hem) <pe...@gmail.com> wrote:
> 
> > >You cannot use a part in a where clause unless you specify the preceeding parts also.
> > But the statement SELECT * FROM mytable WHERE c='myvalue'; works?
> >
> > What are secondary indexes for then if you can't use them in this way?
> 
> What you have in your example is parts of a primary key.
> 
> Secondary are defined in a different way (I never use them because they sort of goes against the whole point of using C* IMHO so I don't know right now how to do it. check the docs)
> 
> Jan
> 
> 
> >
> > Forgot to mention that I am on Cassandra 2.0.1
> >
> > /Petter
> >
> >
> > 2013/10/24 Jan Algermissen <ja...@nordsc.com>
> > Petter,
> >
> > On 24.10.2013, at 14:38, Petter von Dolwitz (Hem) <pe...@gmail.com> wrote:
> >
> > > Hi,
> > >
> > > I have a table that (in simplified version) looks like this:
> > >
> > > CREATE TABLE mytable (
> > >   a varchar,
> > >   b varchar,
> > >   c varchar
> > >   d timstamp,
> > >   e varchar,
> > >   PRIMARY KEY (a, b, c, d)
> > > );
> > >
> > > CREATE INDEX mytable_c_idx ON mytable ( c );
> > >
> > > After populating I execute:
> > >
> > > SELECT * FROM mytable WHERE c='myvalue';
> > >
> > > which works fine. However, using:
> > >
> > > SELECT * FROM mytable WHERE c IN ('myvalue');
> > >
> > > gives me:
> > > Bad Request: PRIMARY KEY part c cannot be restricted (preceding part b is either not restricted or by a non-EQ relation)
> > >
> > > Can anybody explain this?
> >
> > You cannot use a part in a where clause unless you specify the preceeding parts also.
> >
> > Think of it this way: To resolve yur restriction, C* would have to do a full scan over all rows to find those rows that have a 'myvalue' C-part.
> >
> > Jan
> >
> >
> > > My aim is to query for more than one value in the c column. Is this supported?
> > >
> > > Thanks,
> > > Petter
> > >
> > >
> > >
> > >
> > >
> >
> >
> 


Re: Problems using secondary index with IN keyword

Posted by "Petter von Dolwitz (Hem)" <pe...@gmail.com>.
I have defined the secondary index on a field that is part of the primary
key. This should be ok. Maybe you missed the CREATE INDEX bit in my
original post. I might end up not using secondary indexes but since the
feature is there and I need the functionality I would like to know its
limitations and if the behaviour I am experiencing is a problem with my
design, a problem with the data or a problem with Cassandra.

/Petter

Den torsdagen den 24:e oktober 2013 skrev Jan Algermissen:

>
> On 24.10.2013, at 15:13, Petter von Dolwitz (Hem) <
> petter.von.dolwitz@gmail.com <javascript:;>> wrote:
>
> > >You cannot use a part in a where clause unless you specify the
> preceeding parts also.
> > But the statement SELECT * FROM mytable WHERE c='myvalue'; works?
> >
> > What are secondary indexes for then if you can't use them in this way?
>
> What you have in your example is parts of a primary key.
>
> Secondary are defined in a different way (I never use them because they
> sort of goes against the whole point of using C* IMHO so I don't know right
> now how to do it. check the docs)
>
> Jan
>
>
> >
> > Forgot to mention that I am on Cassandra 2.0.1
> >
> > /Petter
> >
> >
> > 2013/10/24 Jan Algermissen <jan.algermissen@nordsc.com <javascript:;>>
> > Petter,
> >
> > On 24.10.2013, at 14:38, Petter von Dolwitz (Hem) <
> petter.von.dolwitz@gmail.com <javascript:;>> wrote:
> >
> > > Hi,
> > >
> > > I have a table that (in simplified version) looks like this:
> > >
> > > CREATE TABLE mytable (
> > >   a varchar,
> > >   b varchar,
> > >   c varchar
> > >   d timstamp,
> > >   e varchar,
> > >   PRIMARY KEY (a, b, c, d)
> > > );
> > >
> > > CREATE INDEX mytable_c_idx ON mytable ( c );
> > >
> > > After populating I execute:
> > >
> > > SELECT * FROM mytable WHERE c='myvalue';
> > >
> > > which works fine. However, using:
> > >
> > > SELECT * FROM mytable WHERE c IN ('myvalue');
> > >
> > > gives me:
> > > Bad Request: PRIMARY KEY part c cannot be restricted (preceding part b
> is either not restricted or by a non-EQ relation)
> > >
> > > Can anybody explain this?
> >
> > You cannot use a part in a where clause unless you specify the
> preceeding parts also.
> >
> > Think of it this way: To resolve yur restriction, C* would have to do a
> full scan over all rows to find those rows that have a 'myvalue' C-part.
> >
> > Jan
> >
> >
> > > My aim is to query for more than one value in the c column. Is this
> supported?
> > >
> > > Thanks,
> > > Petter
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>

Re: Problems using secondary index with IN keyword

Posted by Jan Algermissen <ja...@nordsc.com>.
On 24.10.2013, at 15:13, Petter von Dolwitz (Hem) <pe...@gmail.com> wrote:

> >You cannot use a part in a where clause unless you specify the preceeding parts also.
> But the statement SELECT * FROM mytable WHERE c='myvalue'; works?
> 
> What are secondary indexes for then if you can't use them in this way?

What you have in your example is parts of a primary key.

Secondary are defined in a different way (I never use them because they sort of goes against the whole point of using C* IMHO so I don't know right now how to do it. check the docs)

Jan


> 
> Forgot to mention that I am on Cassandra 2.0.1
> 
> /Petter
> 
> 
> 2013/10/24 Jan Algermissen <ja...@nordsc.com>
> Petter,
> 
> On 24.10.2013, at 14:38, Petter von Dolwitz (Hem) <pe...@gmail.com> wrote:
> 
> > Hi,
> >
> > I have a table that (in simplified version) looks like this:
> >
> > CREATE TABLE mytable (
> >   a varchar,
> >   b varchar,
> >   c varchar
> >   d timstamp,
> >   e varchar,
> >   PRIMARY KEY (a, b, c, d)
> > );
> >
> > CREATE INDEX mytable_c_idx ON mytable ( c );
> >
> > After populating I execute:
> >
> > SELECT * FROM mytable WHERE c='myvalue';
> >
> > which works fine. However, using:
> >
> > SELECT * FROM mytable WHERE c IN ('myvalue');
> >
> > gives me:
> > Bad Request: PRIMARY KEY part c cannot be restricted (preceding part b is either not restricted or by a non-EQ relation)
> >
> > Can anybody explain this?
> 
> You cannot use a part in a where clause unless you specify the preceeding parts also.
> 
> Think of it this way: To resolve yur restriction, C* would have to do a full scan over all rows to find those rows that have a 'myvalue' C-part.
> 
> Jan
> 
> 
> > My aim is to query for more than one value in the c column. Is this supported?
> >
> > Thanks,
> > Petter
> >
> >
> >
> >
> >
> 
> 


Re: Problems using secondary index with IN keyword

Posted by "Petter von Dolwitz (Hem)" <pe...@gmail.com>.
>You cannot use a part in a where clause unless you specify the preceeding
parts also.
But the statement SELECT * FROM mytable WHERE c='myvalue'; works?

What are secondary indexes for then if you can't use them in this way?

Forgot to mention that I am on Cassandra 2.0.1

/Petter


2013/10/24 Jan Algermissen <ja...@nordsc.com>

> Petter,
>
> On 24.10.2013, at 14:38, Petter von Dolwitz (Hem) <
> petter.von.dolwitz@gmail.com> wrote:
>
> > Hi,
> >
> > I have a table that (in simplified version) looks like this:
> >
> > CREATE TABLE mytable (
> >   a varchar,
> >   b varchar,
> >   c varchar
> >   d timstamp,
> >   e varchar,
> >   PRIMARY KEY (a, b, c, d)
> > );
> >
> > CREATE INDEX mytable_c_idx ON mytable ( c );
> >
> > After populating I execute:
> >
> > SELECT * FROM mytable WHERE c='myvalue';
> >
> > which works fine. However, using:
> >
> > SELECT * FROM mytable WHERE c IN ('myvalue');
> >
> > gives me:
> > Bad Request: PRIMARY KEY part c cannot be restricted (preceding part b
> is either not restricted or by a non-EQ relation)
> >
> > Can anybody explain this?
>
> You cannot use a part in a where clause unless you specify the preceeding
> parts also.
>
> Think of it this way: To resolve yur restriction, C* would have to do a
> full scan over all rows to find those rows that have a 'myvalue' C-part.
>
> Jan
>
>
> > My aim is to query for more than one value in the c column. Is this
> supported?
> >
> > Thanks,
> > Petter
> >
> >
> >
> >
> >
>
>

Re: Problems using secondary index with IN keyword

Posted by Jan Algermissen <ja...@nordsc.com>.
Petter,

On 24.10.2013, at 14:38, Petter von Dolwitz (Hem) <pe...@gmail.com> wrote:

> Hi,
> 
> I have a table that (in simplified version) looks like this:
> 
> CREATE TABLE mytable (
>   a varchar,
>   b varchar,
>   c varchar
>   d timstamp,
>   e varchar,
>   PRIMARY KEY (a, b, c, d)
> );
> 
> CREATE INDEX mytable_c_idx ON mytable ( c );
> 
> After populating I execute:
> 
> SELECT * FROM mytable WHERE c='myvalue';
> 
> which works fine. However, using:
> 
> SELECT * FROM mytable WHERE c IN ('myvalue');
> 
> gives me:
> Bad Request: PRIMARY KEY part c cannot be restricted (preceding part b is either not restricted or by a non-EQ relation)
> 
> Can anybody explain this?

You cannot use a part in a where clause unless you specify the preceeding parts also.

Think of it this way: To resolve yur restriction, C* would have to do a full scan over all rows to find those rows that have a 'myvalue' C-part.

Jan


> My aim is to query for more than one value in the c column. Is this supported?
> 
> Thanks,
> Petter
> 
> 
> 
> 
>