Secondary Index and Select

General
1

I can still select rows by non-key (without primary key, partitioning and clustering keys) fields. So, my question is: Whether we still need secondary index or not? And in case we still need, what is the difference?

2

Hi @mld-cih,

A secondary index makes the lookup by the value column very efficient. If you do a lookup on a table without a secondary index, internally the query will end up reading all the data in the table (performing a full scan) in order to lookup your data resulting in higher latencies. So these queries will end up not working with larger data sets.

3

Hi @karthik, thank you for your help. By the way, there are still some points I am not clear so far.

  1. Status of Yugabyte’s secondary index? Is it fully supported or not complete? The document does not mention secondary index.
  2. I see a statement as follows: “create index i1 on test_create_index (r1, r2) covering (c1, c4);”,
    what is the meaning of covering clause?
    Please help me if you know? Thank you!
4

You’re welcome @mld-cih.

  1. YugaByte’s secondary index is well underway. You can follow the progress on this github issue: Support secondary indexes on Apache Cassandra tables in YugaByte database · Issue #21 · yugabyte/yugabyte-db · GitHub
    cc @rpang if he wants to add something.

  2. A covering clause causes the extra columns to be added to the index in order to make lookups faster. Suppose you wrote the query:
    SELECT c1, c4 FROM test_create_index WHERE r1=... AND r2=...
    Normally, the db would fetch the primary keys of the rows matching the condition from the index, and look those up from the table itself. If these columns are in the covering clause, the db can answer straight out of the index.

5

can u explain about secondary index and select

6

Hi @yugalove,

We are going to release secondary indexes soon, and plan to write about this in detail. Please stay tuned.

In the meanwhile, is there a specific question you have that I can answer?

7

When do you release a secondary indexes? How to try it?

1 Like
8

Sorry just seeing this @Shrek, you can try it now on the latest v0.9.9 version of YugaByte. You can see this docs page for more details: