phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bulvik, Noam" <Noam.Bul...@teoco.com>
Subject RE: using index with "or" query
Date Wed, 20 Sep 2017 11:04:53 GMT

Hi James,
Thank you for the fast reply.


1.     As far as we saw in Phoenix documentation UNION is not supported, only UNION ALL. Breaking
the queries to multiple queries with UNION ALL will return duplicates. It may be possible
to wrap these queries with select distinct, but it significantly complicates the queries and
will have performance impact.


2.      In case of select .... Where a='xyz' or b='123'  I don't see how adding column b as
covered column to index on column a will help for finding a row such as the row (a: 'abc',
b: '123') It will help only for a row such as (a: 'xyz', b: '123')


3.     Hint on global index works for a single index. Is there a way to use multiple indexes?
Hint on using multiple indexes? Hint on local index cause an error of unknown field if the
where clause refer to a field in the index and to another field that is not part of the specific
local index. There is an open bug on it.

BR,

Yoav Sapir


On 19 Sep 2017, at 18:21, James Taylor <jamestaylor@apache.org<mailto:jamestaylor@apache.org>>
wrote:
Hi Noam,
A few ideas:
1) Use a UNION instead of an OR and you may be able to use more than one index for one query.
2) Include the columns you're referencing in the index to make it a covered index [1].
3) Hint [2] the query to force the index to be used.

Thanks,
James

[1] http://phoenix.apache.org/secondary_indexing.html#Index_Usage (#1)
[2] http://phoenix.apache.org/secondary_indexing.html#Index_Usage (#2)

On Tue, Sep 19, 2017 at 4:21 AM Bulvik, Noam <Noam.Bulvik@teoco.com<mailto:Noam.Bulvik@teoco.com>>
wrote:
Hi,

We have a case where we have a table with few index on different columns  a, b, c  etc' .
It works well if we do select with "and" condition  (for example select .... Where a='xyz'
and b='123' )but when we have or condition (for example select .... Where a='xyz' or b='123')
we get full scan even though we have index on a and on b.

Is there a way to get this query to use indexes and not full scan beside creating index on
all available column combination (index on a+b , index on a +c ...)


Regards,

Noam


________________________________

PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and confidential, and
is intended only for the use of the individual to whom it is addressed and others who have
been specifically authorized to receive it. If you are not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, or if any problems occur with
transmission, please contact sender. Thank you.

________________________________

PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and confidential, and
is intended only for the use of the individual to whom it is addressed and others who have
been specifically authorized to receive it. If you are not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, or if any problems occur with
transmission, please contact sender. Thank you.

Mime
View raw message