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 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
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 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*
>>
>

Mime
View raw message