NoSQL: Create your first Index on Cassandra Column

In this post, I am sharing the basic details about the Cassandra Indexes.

The architecture of Cassandra Index is very similar to RDBMS Indexes.
Like, The Primary Key column is a default indexed column and don’t require to create additional index on the Primary key column.

You can use CREATE INDEX command to create an index on the particular column.
Cassandra supports the different data types in which you cannot create an Index.

In the older version of CQL, Index must be created on the filtered column and without applying Index on column you cannot use that column as a filter in WHERE clause.

In the Cassandra, it is advisable to create an Index on filtered column.
In the latest version of CQL, if you are trying to access filtered data without an Index, it generates the error like “filtering may have unpredictable performance”. Still, you can SELECT the data without indexes, but you should use ALLOW FILTERING option with SELECT statements.

One of the flexibility of the Cassandra Index is that you can also create a custom index by specifying storage option.

Create a sample Table:

1 2 3 4 5 6 7

CREATE TABLE IF NOT EXISTS tbl_Employee (   EmpID INT PRIMARY KEY   ,EmpFirstName VARCHAR   ,EmpLastName VARCHAR   ,EmpSalary INT );

Insert a Sample record:

1 2 3 4

INSERT INTO tbl_Employee (EmpID,EmpFirstName,EmpLastName,EmpSalary) VALUES (1,'Anvesh','Patel',50000);

SELECT Filtered data without Index:

1	SELECT *FROM tbl_Employee WHERE EmpFirstName='Anvesh';

SELECT data using ALLOW FILTERING option:

1	SELECT *FROM tbl_Employee WHERE EmpFirstName='Anvesh' ALLOW FILTERING;

Create an Index on column:

1 2	CREATE INDEX idx_tbl_Employee_EmpFirstNAme ON dbrnd.tbl_Employee(EmpFirstName);