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 [email protected] <[email protected]>:
> 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 <[email protected]> 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
>>
>
>
