You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@phoenix.apache.org by cmbendre <ch...@zeotap.com> on 2017/06/06 05:41:43 UTC

Index merge / join for multiple where clause

Hi,

I have created a local index for every column in my table. Lets say my
primary key is P_KEY and columns are COL1, COL2, etc

When i do the query - "SELECT P_KEY FROM TABLENAME WHERE COL1='xxx'"
It is scanning only the local index, and returning the results fast.

But when i query - "SELECT P_KEY FROM TABLENAME WHERE COL1='xxx' AND
COL2='yyy'",

I expected that it would still scan two local indexes only and merge the
results. Instead, it is scanning the full table. 

I think there is already the JIRA for this -
https://issues.apache.org/jira/browse/PHOENIX-1801

But considering that such queries are most frequent in our use case, is
Phoenix not suitable for this ?

Thanks,
Chaitanya



--
View this message in context: http://apache-phoenix-user-list.1124778.n5.nabble.com/Index-merge-join-for-multiple-where-clause-tp3637.html
Sent from the Apache Phoenix User List mailing list archive at Nabble.com.

Re: Index merge / join for multiple where clause

Posted by cmbendre <ch...@zeotap.com>.

Hi Jonathan,

I tried the same query using hints - 

/SELECT /*+ INDEX(TABLENAME COL1_LOCAL_INDEX COL2_LOCAL_INDEX) */ P_KEY FROM
TABLENAME WHERE COL1='xxx' AND COL2='yyy'"/

This seems to only take first index into account and throws error - COL2 not
found.

Thanks
Chaitaya



--
View this message in context: http://apache-phoenix-user-list.1124778.n5.nabble.com/Index-merge-join-for-multiple-where-clause-tp3637p3639.html
Sent from the Apache Phoenix User List mailing list archive at Nabble.com.

Re: Index merge / join for multiple where clause

Posted by Jonathan Leech <jo...@gmail.com>.

I'm pretty sure I've worked around that by either using query hints or nesting / subselect. The idea is to use the most selective index 1st, then read and filter by the 2nd column out of the rows in the main table. 

 You can also include additional columns when you build an index if you know the combinations you want in advance.

> On Jun 5, 2017, at 11:41 PM, cmbendre <ch...@zeotap.com> wrote:
> 
> Hi,
> 
> I have created a local index for every column in my table. Lets say my
> primary key is P_KEY and columns are COL1, COL2, etc
> 
> When i do the query - "SELECT P_KEY FROM TABLENAME WHERE COL1='xxx'"
> It is scanning only the local index, and returning the results fast.
> 
> But when i query - "SELECT P_KEY FROM TABLENAME WHERE COL1='xxx' AND
> COL2='yyy'",
> 
> I expected that it would still scan two local indexes only and merge the
> results. Instead, it is scanning the full table. 
> 
> I think there is already the JIRA for this -
> https://issues.apache.org/jira/browse/PHOENIX-1801
> 
> But considering that such queries are most frequent in our use case, is
> Phoenix not suitable for this ?
> 
> Thanks,
> Chaitanya
> 
> 
> 
> --
> View this message in context: http://apache-phoenix-user-list.1124778.n5.nabble.com/Index-merge-join-for-multiple-where-clause-tp3637.html
> Sent from the Apache Phoenix User List mailing list archive at Nabble.com.