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.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.