Question about the performance of the local index in Apache Phoenix

[#1]

Hello, 

I have a question about the performance of the local index in Apache Phoenix 

For example, I have a table TBL_X having 6 columns (A , B ,C ,D E, F).

1. Primary Key is (A, B) 

2. One of the query patterns is   "Select * from  TBL_X where A = 'val1' and C = 'val3' " 

For the better performance of the above query, I try to build the local index. 

Does   [ Local index on (C, A)  ]  perform better than   [ Local index on (C) ]  ?? 

or 

Will there be no performance difference since A is already in the first part of the PK ?? 

[#2 · Accepted solution]

Hello @bgkim 

Thanks for using Cloudera Community. To your Q, the Composite Primary Key would require using both A & B in WHERE Clause as the Indexing is done collectively. As such, Your SELECT Query would ideally benefit upon creating a Local Index on A & C. You may review [1] as Read-Heavy Use-Case benefit via Global Index with Penalty incurred during Writes. Additionally, Phoenix offers Covered Index & Explain Plan helps confirming the Index Usage. Link [2] offers few examples as well. 

With all recommendations, Best Advise is always to review the Performance internally prior to implementing them in Production. 

Regards, Smarak

[1] https://phoenix.apache.org/secondary_indexing.html 

[2] https://learn.microsoft.com/en-us/azure/hdinsight/hbase/apache-hbase-phoenix-performance