phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "rajeshbabu@apache.org" <chrajeshbab...@gmail.com>
Subject Re: Local index not used with non covered queries
Date Thu, 04 Feb 2016 13:02:58 GMT
bq. if the where condition refers to an indexed column and a non indexed
column, it should use the index?

This case also we will not use the index because we need to know the values
of non indexed columns first to apply the filter which we need to get from
the data table. So better to include both the columns in the local index.


Thanks,
Rajeshbabu.

On Thu, Feb 4, 2016 at 5:44 PM, Jacobo Coll <jacobo167@gmail.com> wrote:

> Hi Rajeshbabu,
>
> Thanks for the quick answer!
> I will keep an eye on that issue. I was expecting this to be working at
> least for local indexes.
>
> So, if the where condition refers to an indexed column and a non indexed
> column, it should use the index?
> I have tried this, and its not working for me.
>
> --------------------------------------------------
> create table test_table (mykey varchar primary key, col1 varchar, col2
> varchar);
> create local index idx2 on test_table (col2);
> upsert into test_table (mykey, col1, col2) values('k1', 'v1-1', 'v1-2');
> upsert into test_table (mykey, col1, col2) values('k2', 'v2-1', 'v2-2');
>
> -- select using the indexed column
> select * from test_table where col2 = 'v1-2';
> explain select * from test_table where col2 = 'v1-2';
>
> -- select using the indexed column and the non indexed column
> select * from test_table where col2 = 'v1-2' and col1 = 'v1-1';
> explain select * from test_table where col2 = 'v1-2' and col1 = 'v1-1';
> --------------------------------------------------
>
> The first select is using the index, but the second is not. I don't know if I am missing
something.
>
> Thanks,
> Jacobo Coll
>
>
> 2016-02-04 9:13 GMT+00:00 rajeshbabu@apache.org <chrajeshbabu32@gmail.com>
> :
>
>> Hi Jacobo,
>> The local index will be used if you have any where condition on indexed
>> column otherwise we need to scan index table and data table for each row.
>> That's the reason why it's not using local indexes.
>>
>> There is no index merging currently in Phoenix. There is an improvement
>> task raised for it(PHOENIX-1801
>> <https://issues.apache.org/jira/browse/PHOENIX-1801>).
>>
>> Thanks,
>> Rajeshbabu.
>>
>> On Wed, Feb 3, 2016 at 5:31 PM, Jacobo Coll <jacobo167@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I recently started testing HBase/Phoenix as a storage solution for our
>>> data. The problem is that I am not able to execute some "simple queries". I
>>> am using phoenix 4.5.2 and hbase 1.0.0-cdh5.4.0. After creating a table and
>>> making some selects (full scan), I started using local indexes to
>>> accelerate it. As I know, global indexes only work for covered queries, but:
>>>
>>> Unlike global indexes, local indexes *will* use an index even when all
>>>> columns referenced in the query are not contained in the index.
>>>>
>>>
>>> Knowing this, I prepared a simple test. A table with 3 columns: the
>>> primary key, a locally indexed column and a non indexed column.
>>> Querying by the indexed column works as expected, but if I try to use
>>> both columns, it does a full scan.
>>>
>>> I know that I can include both columns on the index, but this is not
>>> supposed to be required.
>>>
>>> In a previous mail I wrote a more detailed example of the test:
>>> http://mail-archives.apache.org/mod_mbox/phoenix-user/201512.mbox/%3CCAOJgazo-oCahZxsXrdOOsJxm-DVp7SzcNZhDDNJ2DWzkiNhfvA%40mail.gmail.com%3E
>>>
>>> Am I doing something wrong, or missing some step?
>>> Or it is impossible what I am trying to do?
>>> Is there some way to do this without include the other columns in the
>>> index?
>>>
>>> Thanks,
>>> Jacobo Coll
>>>
>>
>>
>

Mime
View raw message