See https://phoenix.apache.org/secondary_indexing.html#Index_Usage. We get this question a fair amount. We have an FAQ, here [1], but it's not a very complete answer (as it doesn't mention hinting or local indexes), so it'd be good if it was updated.
Thanks, James [1] https://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used On Mon, Feb 26, 2018 at 7:43 AM, Josh Elser <els...@apache.org> wrote: > IIRC, Phoenix will only choose to use an index when all columns are > covered (either the index is on the columns or the columns are explicitly > configured to be covered in the DDL). > > > > On 2/26/18 6:45 AM, Alexey Karpov wrote: > >> Hi. >> >> Let’s say I have a table CREATE TABLE test (id integer NOT NULL PRIMARY >> KEY, >> name VARCHAR, description VARCHAR) >> with local index CREATE LOCAL INDEX ix_test_name ON test(name) >> >> For the query SELECT * FROM test WHERE name = 'a' >> it’s all right, Phoenix uses index. >> >> But for the query SELECT * FROM test WHERE name = 'a' AND description = >> 'b' >> It makes full scan over the table. Is there any way to make Phoenix use >> index in such queries(when one field is in index and another is not), >> without including another field into the index ? >> >> I use HDP 2.6.2 and Phoenix 4.7. >> >> Best regards, >> Alexey >> >> >> >> -- >> Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/ >> >>
