phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jacobo Coll <jacobo...@gmail.com>
Subject Re: Local index not used with non covered queries
Date Thu, 04 Feb 2016 12:14:32 GMT
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