Local Indexing and Materialized views in Cassandra 3.0

Prerequisite – Concept of Indexing, Concept of Materialized Views
In this article, we will see how we can do local indexing and how it works and how materialized views works internally.

Let’s discuss one by one.

First, we need to create a table. let’s consider a table Team_data in which id, name, address are the fields.
Let’s have a look.

CREATE TABLE Team_data
(
 id uuid PRIMARY KEY,
 name text,
 address text
); 

Now, insert some data into the table(Team_data). Let’s have a look.

Insert into Team_data(id, name, address) 
values(uuid(), 'ashish gupta', 'mumbai');

Insert into Team_data(id, name, address) 
values(uuid(), 'shivang', 'delhi');

Insert into Team_data(id, name, address) 
values(uuid(), 'gaurav', 'bangalore'); 

Let’s see the results to verify the inserted data.

select * 
from Team_data; 

Output:

Now, here we are going to create local indexing(customer1) in the name column on the Team_data table. let’s have a look.

create index customer1 on Team_data(name); 

Now, here we are going search data that we need.
Let’s have a look.

select address, name 
from Team_data
WHERE name = 'shivang'; 

Output:

Now, here we will see how the above CQL query works and execute graphically.
Let’s have a look.

In the above diagram first, the coordinate node sends the message to all the node across the ring and consider there are three copies of each row of the table(Team_data) then it will search the data on the base of indexing at every node in the ring what we looking for.

Now, here we will see how materialized views of the same table (Team_data) works in the same scenario as discussed above. First, we are going to create materialized views.
Let’s have a look.

create materialized view customer2 as select * 
from Team_data 
where name IS NOT NULL
PRIMARY KEY(name, id); 

Now, again when we will execute CQL query then in materialized views first data will be indexed at every node and it is easier to search the data quickly and also performance will be increased. Let’s have a look.

select address, name from Team_data
WHERE name = 'shivang'; 

Output:

Now, we will see how the above CQL query works for the materialized views in the above same scenario.
Let’s have a look.