phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: smarter using of index while querying?
Date Tue, 10 May 2016 07:06:24 GMT
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, 金砖 <jinzhuan@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, 金砖 <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