phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: 回复: how to use phoenix index to query data?
Date Fri, 24 Oct 2014 06:12:00 GMT
All things being equal (i.e. you're not doing any filtering), in
general it's more efficient to just use the data table, since with a
local index, Phoenix must scan every region.

Try running:
    explain select "col1","col2" from xxc."tb3" where "col1" = 'abc';

or

    explain select "col1","col2" from xxc."tb3" where "col2" = 'NJ';

On Thu, Oct 23, 2014 at 11:03 PM, xuxc1120 <xuxc1120@vip.qq.com> wrote:
> thank u ,
> now i have tried local index,‍
> for the table:
> +------------+------------+------------+------------+
> |   ROWKEY   |    col1    |    col2    |    col3    |
> +------------+------------+------------+------------+
> | 1          | xuxc       | NJ         | NJU      |
> | 2          | abc         | SH         | null       |
>
> *step1:
> ‍
> 0: jdbc:phoenix:localhost> create local index local_col1_tbidx on
> xxc."tb3"("col1");
> 2 rows affected (0.619 seconds)
> 0: jdbc:phoenix:localhost> create local index local_col2_tbidx on
> xxc."tb3"("col2");
> 2 rows affected (0.183 seconds)
> ‍
> *step2:
> 0: jdbc:phoenix:localhost> explain select "col1","col2" from xxc."tb3";
> +------------+
> |    PLAN    |
> +------------+
> | CLIENT PARALLEL 1-WAY FULL SCAN OVER XXC.tb3 |
> +------------+
> 1 row selected (0.036 seconds)
> ‍
> why local index doesn't works?
> and is my SQL incorrect?
> --------------------------------------------------------
> thank you for your kind!
> xuxc
>
> ------------------ 原始邮件 ------------------
> 发件人: "sunfl@certusnet.com.cn";<sunfl@certusnet.com.cn>;
> 发送时间: 2014年10月24日(星期五) 上午10:47
> 收件人: "user"<user@phoenix.apache.org>;
> 主题: Re: 回复: how to use phoenix index to query data?
>
> Fine. If you conduct a select * query, maybe you need to create an index
> including all your cols, cause
>  a global index will only be used if all columns referenced in the query are
> contained by it. Maybe you can
> try local index. Please read the instructions from the site carefully.
>  http://phoenix.apache.org/secondary_indexing.html
>
> ________________________________
> ________________________________
>
> CertusNet
>
> 赛特斯信息科技股份有限公司
>
>
>
>
> 发件人: xuxc1120
> 发送时间: 2014-10-24 10:25
> 收件人: user
> 主题: 回复: 回复: how to use phoenix index to query data?
>
> Now the problem is:
>
> I select * from table where  [condition col1, condition col2, condition
> col3]‍], (maybe that table have 10  cols),
> i create index on every col,such as "create index col1_idx on table(col1);
> create index col2_idx on table(col2); ...  create index col10_idx on
> table(col10)‍";
> and then i execute the query sql "select * from table where  [condition
> col1, condition col2, condition col3],‍" .
> for 1.8million rows ,it will takes 10sec , as long as use Filter directly in
> hbase shell.
> i 'd rather know something wrong i have done.
>
>
>
> ------------------ 原始邮件 ------------------
> 发件人: "sunfl@certusnet.com.cn";<sunfl@certusnet.com.cn>;
> 发送时间: 2014年10月24日(星期五) 上午10:09
> 收件人: "user"<user@phoenix.apache.org>;
> 主题: Re: 回复: how to use phoenix index to query data?
>
> well. Global index shall need you to include all your query condition
> columns to facilitate index usage.
> e.g. If you create an index on table (col1), then you may only conduct such
> queries as select col1 from table where [condition col1]
> If you want to include more query results columns, you can create index on
> table (col1) include (col2,col3) as instructions here.
>
> ________________________________
> ________________________________
>
> CertusNet
>
>
> 发件人: xuxc1120
> 发送时间: 2014-10-24 09:52
> 收件人: user
> 主题: 回复: Re: how to use phoenix index to query data?
> i use "create index "name_idx" on table("info".qulifier");
> it is a global index.‍
> so ,how to use "name_idx" to speed query?‍
>
> ------------------ 原始邮件 ------------------
> 发件人: "sunfl@certusnet.com.cn";<sunfl@certusnet.com.cn>;
> 发送时间: 2014年10月24日(星期五) 上午9:00
> 收件人: "user"<user@phoenix.apache.org>;
> 主题: Re: Re: how to use phoenix index to query data?
>
> You may need to confirm what kind of your index type. global index or local
> index, cause they differ a lot in usage.
> Explain command shall tell you whether index table works.
>
> ________________________________
> ________________________________
>
> CertusNet
>
>
>
> From: Eli Levine
> Date: 2014-10-23 23:04
> To: user
> Subject: Re: how to use phoenix index to query data?
> The EXPLAIN command should help.
> http://phoenix.apache.org/language/index.html#explain
> On Thu, Oct 23, 2014 at 5:40 AM xuxc1120 <xuxc1120@vip.qq.com> wrote:
>>
>> environment:
>> hadoop-2.3.0-cdh-5.1.0
>> hbase-0.98.1-cdh-5.1.0
>> phoenix-4.1.0‍
>>
>> i have create a table in phoenix client ,and then create index on some
>> columns, after that ,i insert data to table,
>> the data have inserted successful and the index table also have records,
>>
>> but when i use "select * from tb1 where condition=1 and condition2=2"
>> clause in the phoenix client , for 180W data, it takes almost 10sec, i want
>> to know if the index works?
>>
>

Mime
View raw message