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
painless: http://phoenix.incubator.apache.org/upgrade_from_2_2.html
Thanks,
James
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
"metadata_test"("info"."appid","info"."counterid","info"."time");
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" =
'test_counter';
+------------+
| PLAN |
+------------+
| CLIENT PARALLEL 1-WAY RANGE SCAN OVER test_index
['test_app','test_counter'] |
+------------+
1 row selected (0.023 seconds)
On Sat, May 3, 2014 at 10:43 AM, yixuan geng <[email protected]> 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 <[email protected]> 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 [mailto:[email protected]]
>> *Sent:* Sat 5/3/2014 1:03 PM
>> *To:* [email protected]
>> *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*
>>
>
