Support Questions
Find answers, ask questions, and share your expertise

Apache phoenix issue with condition on multiple Secondary LOCAL Index columns

New Contributor

Lets say I have a immutable table(USERS) with data up to 30 billion like,

table USERS with 26 columns say A, B, C, D ,,,  X, Y, Z. Out of which A, B, C are my primary keys and I added the LOCAL index on P and Q column.

CREATE LOCAL INDEX USERS_INDEX_P ON USERS  (P);

CREATE LOCAL INDEX USERS_INDEX_Q ON USERS  (Q);

 

lets say I have following few records in table like,

ABC PQ Z
a1b1c1 p1q1 z1
a2b2c2 p2q2 z2
a3b3c3 p3q3 z3

 

Now following query shows expected results in millisecond due to index

Query-1 ==> select * from USERS where P = 'p1';   // 1 result found

Query-2 ==> select * from USERS where Q = 'q1';  // 1 result found

 

But if I add condition on both the columns(local indexed) then it shows 0 results.

select * from USERS where P = 'p1' and Q = 'q1';  // 0 result found even though there is record available in DB for this combination.

 

Please let me know how can I cope up with this situation or any alternative available ?

If I only index column P and then add second condition on column Q then it takes more time(10+ seconds).

 

 

0 REPLIES 0