phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From 金砖 <jinzh...@wacai.com>
Subject Re: smarter using of index while querying?
Date Tue, 10 May 2016 07:05:01 GMT
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, 金砖 <jinzhuan@wacai.com 
> <mailto:jinzhuan@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)            |
>     +-------------------------------------------------------------+
>
>


Mime
View raw message