phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: using index with "or" query
Date Wed, 20 Sep 2017 16:01:42 GMT
If you're using local index, then the hint isn't necessary. However, a
covered local index may improve performance (at the expense of extra space)
by preventing a local get to retrieve the data row (when a column not in
the index is referenced).

Phoenix will only use a single index. The only exception is with UNION ALL.

Thanks,
James

On Tue, Sep 19, 2017 at 12:27 PM Sapir, Yoav <Yoav.Sapir@teoco.com> wrote:

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