phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <>
Subject Re: Secondary index is not used
Date Sat, 03 May 2014 18:07:15 GMT
Yes, Yixuan you're right - all your columns are in the index so it should
be used. I tested this with our 3.0.0 release (not sure what your CREATE
TABLE statement look like, though) and it works fine (see below), so you
may be running into a bug in our 2.2.2 release. Upgrade is easy and


0: jdbc:phoenix:localhost> create table "metadata_test"("info"."appid"
VARCHAR, "info"."counterid" VARCHAR, "info"."time" DATE, k VARCHAR PRIMARY
KEY) immutable_rows=true;
No rows affected (2.228 seconds)
0: jdbc:phoenix:localhost> create index "test_index" on
No rows affected (1.227 seconds)
0: jdbc:phoenix:localhost> explain select "info"."appid" from
"metadata_test" where "info"."appid" = 'test_app' and "info"."counterid" =
|    PLAN    |
['test_app','test_counter'] |
1 row selected (0.023 seconds)

On Sat, May 3, 2014 at 10:43 AM, yixuan geng <> wrote:

> hi Job,
> In the example I provided, I think all columns in the query have been
> covered by the index, right?
> Best,
> Yixuan
> On Saturday, May 3, 2014, Job Thomas <> wrote:
>> In Phoenix if you select any column apart from indexed column it will
>> perform a full scan to get the resut.
>> But you can include required columns in the same index created.
>> If you don't want to include column in the index table due to space
>> utilization or dynamic query , you can perform a work arouond for this.
>> Get the primary key from indexed table and using that key query the main
>> table to get required columns. once phoenix supports subquery we can achive
>> the same in one subquery.
>>  Thanks & Regards
>> Job M Thomas
>> ------------------------------
>> *From:* yixuan geng []
>> *Sent:* Sat 5/3/2014 1:03 PM
>> *To:*
>> *Subject:* Secondary index is not used
>>  Hi all,
>>  I am using the great secondary index feature on an existing immutable
>> table. I was able to successfully create the index and actually saw the
>> index table in hbase.
>>  However, when I do "explain {query}", I always get " x-way full scan"
>> which I believe means the index is not actually used.
>>  I know people have asked similar questions and the root cause turned out
>> to be that the columns used in the query is not a subset of the columns
>> defined/included in the index. But I am pretty sure my index has covered
>> all the columns in the query.
>>  Here is the example:
>>  I have an existing table in hbase named "metadata_test", it has only one
>> column family "info". There are already some data rows in the table.
>>  1. Since the table is "write once, append only", I did:
>>  *ALTER TABLE \"sgtrack_metadata_test\" SET IMMUTABLE_ROWS=true*
>> 2. Then I created an index (info.appid, info.counterid, info.time) on
>> this table as following:
>> *create index \"test_index\" on \"metadata_test\" ( \"info\".\"appid*

View raw message