CODEWITHSUNDEEP
cassandradatastax-java-drivercqlsh
Arosha
Arosha

Reputation: 81

Why cassandra/cql restrict to use where clause on a column that not indexed?

I have a table as follows in Cassandra 2.0.8:

CREATE TABLE emp ( empid int, deptid int, first_name text, last_name text, PRIMARY KEY (empid, deptid) )

when I try to search by: "select * from emp where first_name='John';"

cql shell says:
"Bad Request: No indexed columns present in by-columns clause with Equal operator"

I searched for the issue and every places it says add a secondary index for the column 'first_name'.

But I need to know the exact reason for why that column need to be indexed? Only thing I can figure out is performance. Any other reasons?

Upvotes: 0

Views: 3815

Answers (2)

Dhruv Saksena
Dhruv Saksena

Reputation: 219

Alternatively you can create an index in Cassandra, but that will hamper your write performance.

Upvotes: 0

Jacek L.
Jacek L.

Reputation: 1416

Cassandra does not support for searching by arbitrary column. It is because it would involve scanning all the rows, which is not supported.

The data are internally organised into something which one can compare to HashMap[X, SortedMap[Y, Z]]. The key of the outer map is a partition key value and the key of the inner map is a kind of concatenation of all clustering columns values and a name of some regular column.

Unless you have an index on a column, you need to provide full (preferred) or partial path to the data you want to collect with the query. Therefore, you should design your schema so that queries contain primary key value and some range on clustering columns.

You may read about what is allowed and what is not here

Upvotes: 1

Related Questions