No, the index can’t be used .
> On Aug 21, 2019, at 2:38 AM, Ankit Singhal <ankitsingha...@gmail.com
> <mailto:ankitsingha...@gmail.com>> wrote:
>
> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
> (Has been filled data with bulkload and index is active)
>
> Query:
> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from
> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>
> The first query will use index local_c_h_index and result shortly, the second
> query won’t , and response slowly.
> Yeah, the local index should be used in both the cases, looks like a bug to
> me, can you please raise a JIRA in Phoenix project for the same.
> QueryOptimizer.java may have a relevant code to fix the issue, so the patch
> would really be appreciated.
>
> And, also can you try running "select a,b,c,d,e,f,g,h,i,j,k,m,n from
> TEST_PHOENIX.APP where c=2 and h = 1 limit 5", and see if index is getting
> used.
>
> Regards,
> Ankit Singhal
>
> On Tue, Aug 20, 2019 at 1:49 AM you Zhuang <zhuangzixiao...@gmail.com
> <mailto:zhuangzixiao...@gmail.com>> wrote:
> Er, I also read the sentence “Unlike global indexes, local indexes will use
> an index even when all columns referenced in the query are not contained in
> the index. This is done by default for local indexes because we know that the
> table and index data co-reside on the same region server thus ensuring the
> lookup is local.”
>
> I ‘m totally confused.
>
>
>> On Aug 20, 2019, at 12:32 AM, Josh Elser <els...@apache.org
>> <mailto:els...@apache.org>> wrote:
>>
>> http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used
>> <http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used>
>>
>> On 8/19/19 6:06 AM, you Zhuang wrote:
>>> Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
>>> hbase-version: 1.4.6
>>> Table:
>>> CREATE TABLE test_phoenix.app (
>>> dt integer not null,
>>> a bigint not null ,
>>> b bigint not null ,
>>> c bigint not null ,
>>> d bigint not null ,
>>> e bigint not null ,
>>> f bigint not null ,
>>> g bigint not null ,
>>> h bigint not null ,
>>> i bigint not null ,
>>> j bigint not null ,
>>> k bigint not null ,
>>> m decimal(30,6) ,
>>> n decimal(30,6)
>>> CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
>>> ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 300000;
>>> Index:
>>> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
>>> (Has been filled data with bulkload and index is active)
>>> Query:
>>> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from
>>> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>>> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>>> The first query will use index local_c_h_index and result shortly, the
>>> second query won’t , and response slowly.
>>> The explain plan is weird, all showing without using index.
>>>> On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka <asaras...@eztexting.com
>>>> <mailto:asaras...@eztexting.com> <mailto:asaras...@eztexting.com
>>>> <mailto:asaras...@eztexting.com>>> wrote:
>>>>
>>>> We have no problems with that. I mean indexes are used even without hints,
>>>> if they're suitable for a query.
>>>> Maybe you can share your Phoenix version, query, index definition and exec
>>>> plan ?
>>>>
>>>> On Mon, Aug 19, 2019 at 12:46 PM you Zhuang <zhuangzixiao...@gmail.com
>>>> <mailto:zhuangzixiao...@gmail.com> <mailto:zhuangzixiao...@gmail.com
>>>> <mailto:zhuangzixiao...@gmail.com>>> wrote:
>>>>
>>>> Yeah, I mean no hint , use appropriate index automatically. I
>>>> create a local index and a query with corresponding index column
>>>> filter in where clause. But the query doesn’t use index, with
>>>> index hint it uses it.
>>>>
>>>>
>>>>
>>>> --
>>>> Aleksandr Saraseka
>>>> DBA
>>>> 380997600401
>>>> <tel:380997600401 <tel:380997600401>> *•* asaras...@eztexting.com
>>>> <mailto:asaras...@eztexting.com> <mailto:asaras...@eztexting.com
>>>> <mailto:asaras...@eztexting.com>> *•* eztexting.com
>>>> <http://eztexting.com/><http://eztexting.com/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature
>>>>
>>>> <http://eztexting.com/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>>
>>>>
>>>>
>>>> <http://facebook.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature
>>>>
>>>> <http://facebook.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>>
>>>>
>>>> <http://linkedin.com/company/eztexting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature
>>>>
>>>> <http://linkedin.com/company/eztexting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>>
>>>>
>>>> <http://twitter.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature
>>>>
>>>> <http://twitter.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>>
>>>>
>>>> <https://www.youtube.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature
>>>>
>>>> <https://www.youtube.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>>
>>>>
>>>> <https://www.instagram.com/ez_texting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature
>>>>
>>>> <https://www.instagram.com/ez_texting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>>
>>>>
>>>> <https://www.facebook.com/alex.saraseka?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature
>>>>
>>>> <https://www.facebook.com/alex.saraseka?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>>
>>>>
>>>> <https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature
>>>>
>>>> <https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>>
>