Support Questions

Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.

Question about the performance of the local index in Apache Phoenix

New Contributor

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 ?? 

 

 

1 ACCEPTED SOLUTION

Super Collaborator

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 

View solution in original post

1 REPLY 1

Super Collaborator

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 

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.