Hi James, Thanks for your reply. Looks like you created a new phoenix table and feed data into int. In my case, I created a phoenix view on an existing populated hbase table. I wonder if that changes the equation?
What I did: I had an hbase table named "metadata_test". The table was created as "*create 'metatadata_test', 'info' *". Some data has been populated into the table. So I created a phoenix *view* on top of this hbase table by create view \"metadata_test\" (pk VARCHAR PRIMARY KEY, \"info\".\"appid\" VARCHAR,\"info\".\"counterid\" VARCHAR,\"info\".\"time\" VARCHAR,\"info\".\" userid\" VARCHAR,\"info\".\"version\" VARCHAR)" Then I create followed the steps in my original post to create the index. Do you think the index should work in this case? If so , I will go check out the upgrade. Thanks, Yixuan 2014-05-03 11:07 GMT-07:00 James Taylor <[email protected]>: > 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* >>> >> >
