What Situation a Secondary Index used in Cassandra

Following are the step where a situation occurs for secondary index.

1) :- I am creating a table exam_year_name can store the rank of exam for exam.

cqlsh:payroll> CREATE TABLE exam_year_name (
           ...   exam_year int,
           ...   exam_name text,
           ...   examlist_name text,
           ...   rank int,
           ...   PRIMARY KEY ((exam_year, exam_name), rank)
           ... );
cqlsh:payroll> Select * from exam_year_name;

 exam_year | exam_name | rank | examlist_name
-----------+-----------+------+---------------

(0 rows)
cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank)
           ... VALUES (2015,'Teacher Eligibility Test', 'CTET',1);
cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank)
           ... VALUES (2015,'Teacher Eligibility Test', 'UPTET',2);
cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank)
           ... VALUES (2015,'Teacher Eligibility Test', 'MPTET',3);
cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank)
           ... VALUES (2015,'Public Service Commission', 'UPPSC',1);
cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank)
           ... VALUES (2015,'Public Service Commission', 'PBPSC',2);
cqlsh:payroll> INSERT INTO exam_year_name (exam_year, exam_name ,examlist_name,rank)
           ... VALUES (2015,'Public Service Commission', 'MPPSC',3);
cqlsh:payroll>
cqlsh:payroll>
cqlsh:payroll> select * from exam_year_name;

 exam_year | exam_name                 | rank | examlist_name
-----------+---------------------------+------+---------------
      2015 | Public Service Commission |    1 |         UPPSC
      2015 | Public Service Commission |    2 |         PBPSC
      2015 | Public Service Commission |    3 |         MPPSC
      2015 |  Teacher Eligibility Test |    1 |          CTET
      2015 |  Teacher Eligibility Test |    2 |         UPTET
      2015 |  Teacher Eligibility Test |    3 |         MPTET

(6 rows)

2) :- Both exam_year and exam_name are specified as comprise the partition key.

cqlsh:payroll> select * from exam_year_name where exam_year=2015 and exam_name='Public Service Commission';

 exam_year | exam_name                 | rank | examlist_name
-----------+---------------------------+------+---------------
      2015 | Public Service Commission |    1 |         UPPSC
      2015 | Public Service Commission |    2 |         PBPSC
      2015 | Public Service Commission |    3 |         MPPSC

(3 rows)

3) :- I am query to listing of the rankings for a particular year.  This query will fail if only the first column is used in the conditional operator because the table has a composite partition key.

cqlsh:payroll> select * from exam_year_name where exam_year=2015;

Fail to execute
InvalidRequest: Error from server: code=2200 [Invalid query]
message="Partition key parts: exam_name must be restricted as other parts are"

4) :- At this situation, we need secondary index is created for the exam_year, and the query will succeed.

cqlsh:payroll> CREATE INDEX eyear ON exam_year_name (exam_year);
cqlsh:payroll> select * from exam_year_name where exam_year=2015;

 exam_year | exam_name                 | rank | examlist_name
-----------+---------------------------+------+---------------
      2015 | Public Service Commission |    1 |         UPPSC
      2015 | Public Service Commission |    2 |         PBPSC
      2015 | Public Service Commission |    3 |         MPPSC
      2015 |  Teacher Eligibility Test |    1 |          CTET
      2015 |  Teacher Eligibility Test |    2 |         UPTET
      2015 |  Teacher Eligibility Test |    3 |         MPTET

(6 rows)

5) :- A clustering column can also be used to create an index. An index is created on rank, and used in a query.

cqlsh:payroll> CREATE INDEX erank ON exam_year_name (rank);

cqlsh:payroll> select * from exam_year_name where rank=1;

 

 exam_year | exam_name                 | rank | examlist_name

-----------+---------------------------+------+---------------

      2015 | Public Service Commission |    1 |         UPPSC

      2015 |  Teacher Eligibility Test |    1 |          CTET

 

(2 rows)

cqlsh:payroll>