phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Secondary index is not used
Date Sun, 04 May 2014 00:04:39 GMT
You can create a table over an existing HBase table too, so change "view"
to "table" and it will work fine. For immutable table indexes to be
maintained, the updates must come in through Phoenix APIs. Since a view
over an existing HBase table is read-only, it usually doesn't make sense
for it to have a secondary index.

Thanks,
James

On Saturday, May 3, 2014, yixuan geng <gengyx@soulgame.com> wrote:

> 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 <jamestaylor@apache.org<javascript:_e(%7B%7D,'cvml','jamestaylor@apache.org');>
> >:
>
> 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 <gengyx@soulgame.com> 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 <jobt@suntecgroup.com> 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:gengyx@soulgame.com]
> *Sent:* Sat 5/3/2014 1:03 PM
> *To:* user@phoenix.incubator.apache.org
> *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 i
>
>
>

Mime
View raw message