You are right, thanks so much, but it’s so limited that I can’t include all columns due to hundreds of columns existing in one table.
I think covered columns must be queried in global indexes is reasonable, local index isn’t. Because we query rowkey from local index first , then get actual row from data table. Thus we have no necessity to limit local index usage including all queried columns. > On Aug 20, 2019, at 12:32 AM, Josh Elser <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>>
