Cassandra table - select query with where clause

Question

I am trying to create a table in Cassandra. CREATE TABLE users ( user_name varchar PRIMARY KEY, password varchar, gender varchar, session_token varchar, state varchar, birth_year bigint );

Now If I want to query the table username and password where my session_token = 1000001

Select user_name,password from users where session_token = 1000001; Will this query work or do I have to create an index on session_token first and then do select query?

Is is possible that for a single session_token there are multiple username ?? — Ashraful Islam
– Ashraful Islam, Commented Apr 27, 2017 at 8:19

Ashraful Islam · Accepted Answer · 2017-04-27 08:30:48Z

You have to first create index on it.

Remember When not to use an index :

On high-cardinality columns because you then query a huge volume of records for a small number of results
On Low-cardinality column
In tables that use a counter column
On a frequently updated or deleted column
To look for a row in a large partition unless narrowly queried. See Problems using an index to look for a row in a large partition unless narrowly queried below.

In your case session_token seems to be high cardinality column

It is best to create another table :

CREATE TABLE user_by_session ( session_token varchar, user_name varchar, password varchar, PRIMARY KEY(session_token, user_name) );

Now you can query :

SELECT user_name, password FROM user_by_session WHERE session_token = '1000001';

Source : http://docs.datastax.com/en/cql/3.1/cql/ddl/ddl_when_use_index_c.html#concept_ds_sgh_yzz_zj

Collectives™ on Stack Overflow

Cassandra table - select query with where clause

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related