You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by 金砖 <ji...@wacai.com> on 2016/05/10 06:36:07 UTC
smarter using of index while querying?
hi, I'm using phoenix-4.7, and I found phoenix did not use index
efficiently enough.
Can phoenix optimize more for this situation?
EX:
table:
create table t (pk varchar primary key, name varchar, age
unsigned_int);
and create 2 index on it:
create index name on t (name);
create index age on t (age);
*
problem1: select * from table did not use index, instead did full scan
on table.*
0: jdbc:phoenix:localhost> explain select * from t where name = 'name';
+-------------------------------------------------------------+
| PLAN |
+-------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
| SERVER FILTER BY NAME = 'name' |
+-------------------------------------------------------------+
*problem2: when selecting more than 1 field, neither index will be used. *
0: jdbc:phoenix:localhost> explain select name from t where name =
'name' and age > 1;
+-------------------------------------------------------------+
| PLAN |
+-------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
| SERVER FILTER BY (NAME = 'name' AND AGE > 1) |
+-------------------------------------------------------------+
0: jdbc:phoenix:localhost> explain select age from t where name = 'name'
and age > 1;
+-------------------------------------------------------------+
| PLAN |
+-------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
| SERVER FILTER BY (NAME = 'name' AND AGE > 1) |
+-------------------------------------------------------------+
Re: smarter using of index while querying?
Posted by James Taylor <ja...@apache.org>.
It's not always better to use the index when you have to join back to the
data table. It depends how selective the index is.
On Tue, May 10, 2016 at 12:05 AM, 金砖 <ji...@wacai.com> wrote:
> thanks James!
> Hint the query to force use index works.
> but can this be more automatic?
> I mean obviously with case like problem1, using index NAME will be better
> ?
>
>
> 在 2016年05月10日 14:56, James Taylor 写道:
>
> Please read the following and hopefully it will answer your questions:
> https://phoenix.apache.org/secondary_indexing.html#Index_Usage
>
> On Mon, May 9, 2016 at 11:36 PM, 金砖 <ji...@wacai.com> wrote:
>
>> hi, I'm using phoenix-4.7, and I found phoenix did not use index
>> efficiently enough.
>> Can phoenix optimize more for this situation?
>>
>> EX:
>>
>> table:
>>
>> create table t (pk varchar primary key, name varchar, age
>> unsigned_int);
>>
>> and create 2 index on it:
>> create index name on t (name);
>> create index age on t (age);
>>
>>
>> * problem1: select * from table did not use index, instead did full
>> scan on table.*
>>
>> 0: jdbc:phoenix:localhost> explain select * from t where name = 'name';
>> +-------------------------------------------------------------+
>> | PLAN |
>> +-------------------------------------------------------------+
>> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
>> | SERVER FILTER BY NAME = 'name' |
>> +-------------------------------------------------------------+
>>
>>
>> *problem2: when selecting more than 1 field, neither index will be
>> used. *
>>
>> 0: jdbc:phoenix:localhost> explain select name from t where name = 'name'
>> and age > 1;
>> +-------------------------------------------------------------+
>> | PLAN |
>> +-------------------------------------------------------------+
>> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
>> | SERVER FILTER BY (NAME = 'name' AND AGE > 1) |
>> +-------------------------------------------------------------+
>>
>>
>> 0: jdbc:phoenix:localhost> explain select age from t where name = 'name'
>> and age > 1;
>> +-------------------------------------------------------------+
>> | PLAN |
>> +-------------------------------------------------------------+
>> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
>> | SERVER FILTER BY (NAME = 'name' AND AGE > 1) |
>> +-------------------------------------------------------------+
>>
>>
>
>
Re: smarter using of index while querying?
Posted by 金砖 <ji...@wacai.com>.
thanks James!
Hint the query to force use index works.
but can this be more automatic?
I mean obviously with case like problem1, using index NAME will be better ?
\u5728 2016\u5e7405\u670810\u65e5 14:56, James Taylor \u5199\u9053:
> Please read the following and hopefully it will answer your questions:
> https://phoenix.apache.org/secondary_indexing.html#Index_Usage
>
> On Mon, May 9, 2016 at 11:36 PM, \u91d1\u7816 <jinzhuan@wacai.com
> <ma...@wacai.com>> wrote:
>
> hi, I'm using phoenix-4.7, and I found phoenix did not use index
> efficiently enough.
> Can phoenix optimize more for this situation?
>
> EX:
>
> table:
>
> create table t (pk varchar primary key, name varchar, age
> unsigned_int);
>
> and create 2 index on it:
> create index name on t (name);
> create index age on t (age);
>
> *
> problem1: select * from table did not use index, instead did
> full scan on table.*
>
> 0: jdbc:phoenix:localhost> explain select * from t where name =
> 'name';
> +-------------------------------------------------------------+
> | PLAN |
> +-------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
> | SERVER FILTER BY NAME = 'name' |
> +-------------------------------------------------------------+
>
>
> *problem2: when selecting more than 1 field, neither index will
> be used. *
>
> 0: jdbc:phoenix:localhost> explain select name from t where name =
> 'name' and age > 1;
> +-------------------------------------------------------------+
> | PLAN |
> +-------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
> | SERVER FILTER BY (NAME = 'name' AND AGE > 1) |
> +-------------------------------------------------------------+
>
>
> 0: jdbc:phoenix:localhost> explain select age from t where name =
> 'name' and age > 1;
> +-------------------------------------------------------------+
> | PLAN |
> +-------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
> | SERVER FILTER BY (NAME = 'name' AND AGE > 1) |
> +-------------------------------------------------------------+
>
>
Re: smarter using of index while querying?
Posted by James Taylor <ja...@apache.org>.
Please read the following and hopefully it will answer your questions:
https://phoenix.apache.org/secondary_indexing.html#Index_Usage
On Mon, May 9, 2016 at 11:36 PM, 金砖 <ji...@wacai.com> wrote:
> hi, I'm using phoenix-4.7, and I found phoenix did not use index
> efficiently enough.
> Can phoenix optimize more for this situation?
>
> EX:
>
> table:
>
> create table t (pk varchar primary key, name varchar, age
> unsigned_int);
>
> and create 2 index on it:
> create index name on t (name);
> create index age on t (age);
>
>
> * problem1: select * from table did not use index, instead did full scan
> on table.*
>
> 0: jdbc:phoenix:localhost> explain select * from t where name = 'name';
> +-------------------------------------------------------------+
> | PLAN |
> +-------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
> | SERVER FILTER BY NAME = 'name' |
> +-------------------------------------------------------------+
>
>
> *problem2: when selecting more than 1 field, neither index will be used.
> *
>
> 0: jdbc:phoenix:localhost> explain select name from t where name = 'name'
> and age > 1;
> +-------------------------------------------------------------+
> | PLAN |
> +-------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
> | SERVER FILTER BY (NAME = 'name' AND AGE > 1) |
> +-------------------------------------------------------------+
>
>
> 0: jdbc:phoenix:localhost> explain select age from t where name = 'name'
> and age > 1;
> +-------------------------------------------------------------+
> | PLAN |
> +-------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER T |
> | SERVER FILTER BY (NAME = 'name' AND AGE > 1) |
> +-------------------------------------------------------------+
>
>