CODEWITHSUNDEEP
cassandracqlcqlsh
chrisTina
chrisTina

Reputation: 2378

cassandra error when using select and where in cql

I have a cassandra table defined like this: 

CREATE TABLE test.test(
id text,
time bigint,
tag text,
mstatus boolean,
lonumb  int,
PRIMARY KEY (id, time, tag)
)

And I want to select one column using select. I tried:

select * from test where lonumb = 4231;

It gives:

code=2200 [Invalid query] message="No indexed columns present in by-columns clause with Equal operator"

Also I cannot do

select * from test where mstatus = true;

Doesn't cassandra support where as a part of CQL? How to correct this?

Upvotes: 2

Views: 993

Answers (2)

jny
jny

Reputation: 8067

You can only use WHERE on the indexed or primary key columns. To correct your issue you will need to create an index.

CREATE INDEX iname 
ON keyspacename.tablename(columname)

You can see more info here.

But you have to keep in mind that this query will have to run against all nodes in the cluster.

Alternatively you might rethink your table structure if the lonumb is something you'll do the most queries on.

Upvotes: 3

Aaron
Aaron

Reputation: 57808

Jny is correct in that WHERE is only valid on columns in the PRIMARY KEY, or those where a secondary index has been created for. One way to solve this issue is to create a specific query table for lonumb queries.

CREATE TABLE test.testbylonumb(
  id text,
  time bigint,
  tag text,
  mstatus boolean,
  lonumb  int,
  PRIMARY KEY (lonumb, time, id)
)

Now, this query will work:

select * from testbylonumb where lonumb = 4231;

It will return all CQL rows where lonumb = 4231, sorted by time. I put id on the PRIMARY KEY to ensure uniqueness.

select * from test where mstatus = true;

This one is trickier. Indexes and keys on low-cardinality columns (like booleans) are generally considered a bad idea. See if there's another way you could model that. Otherwise, you could experiment with a secondary index on mstatus, but only use it when you specify a partition key (lonumb in this case), like this:

select * from testbylonumb where lonumb = 4231 AND mstatus = true;

Maybe that wouldn't perform too badly, as you are restricting it to a specific partition. But I definitely wouldn't ever do a SELECT * on mstatus.

Upvotes: 2

Related Questions