You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Alexey Karpov <ka...@yandex.ru> on 2018/02/26 11:45:05 UTC
Secondary index question
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/
Re: Secondary index question
Posted by Alexey Karpov <ka...@yandex.ru>.
Created https://issues.apache.org/jira/browse/PHOENIX-4632
--
Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
Re: Secondary index question
Posted by Miles Spielberg <mi...@box.com>.
I believe this is the same bug we encountered with local indexes where Phoenix 4.13.1 failed to join back to the main table if the local index is not covering for all columns referenced in the WHERE.
> On Feb 27, 2018, at 9:36 AM, James Taylor <ja...@apache.org> wrote:
>
> Please file a JIRA and include the Phoenix and HBase version. Sounds like you�fve found a bug.
>
>> On Tue, Feb 27, 2018 at 9:21 AM Jonathan Leech <jo...@gmail.com> wrote:
>> I�fve done what you�fre looking for by selecting the pk from the index in a nested query and filtering the other column separately.
>>
>> > On Feb 27, 2018, at 6:39 AM, Alexey Karpov <ka...@yandex.ru> wrote:
>> >
>> > Thanks for quick answer, but my case is a slightly different. I've seen these
>> > links and already use local index. All cases, described in faq, index_usage
>> > and any other, I've found in this user list, are about SELECT clause. In
>> > WHERE clause there is always field from the index.
>> >
>> > In my case in WHERE clause I have one field from the index and one not from
>> > the index, combined with AND operator:
>> > SELECT * from test WHERE name = 'a' *AND description = 'b'*
>> > name - from the index
>> > description - not from the index
>> >
>> > Without filter on description (only on name) Phoenix uses index, as expected
>> > for local index. But with additional filter Phoenix decides to do a full
>> > scan. And my question is: Is there any way to make Phoenix use index in such
>> > types of queries, without include all fields in index ?
>> >
>> > Hint does not help:
>> > SELECT /*+ INDEX(test ix_test_name) */ name FROM test WHERE name = 'a' AND
>> > description= 'b'
>> > ColumnNotFoundException: ERROR 504 (42703): Undefined column.
>> > columnName=DESCRIPTION.
>> >
>> > Thanks,
>> > Alexey.
>> >
>> >
>> >
>> > --
>> > Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
Re: Secondary index question
Posted by James Taylor <ja...@apache.org>.
Please file a JIRA and include the Phoenix and HBase version. Sounds like
you’ve found a bug.
On Tue, Feb 27, 2018 at 9:21 AM Jonathan Leech <jo...@gmail.com> wrote:
> I’ve done what you’re looking for by selecting the pk from the index in a
> nested query and filtering the other column separately.
>
> > On Feb 27, 2018, at 6:39 AM, Alexey Karpov <ka...@yandex.ru> wrote:
> >
> > Thanks for quick answer, but my case is a slightly different. I've seen
> these
> > links and already use local index. All cases, described in faq,
> index_usage
> > and any other, I've found in this user list, are about SELECT clause. In
> > WHERE clause there is always field from the index.
> >
> > In my case in WHERE clause I have one field from the index and one not
> from
> > the index, combined with AND operator:
> > SELECT * from test WHERE name = 'a' *AND description = 'b'*
> > name - from the index
> > description - not from the index
> >
> > Without filter on description (only on name) Phoenix uses index, as
> expected
> > for local index. But with additional filter Phoenix decides to do a full
> > scan. And my question is: Is there any way to make Phoenix use index in
> such
> > types of queries, without include all fields in index ?
> >
> > Hint does not help:
> > SELECT /*+ INDEX(test ix_test_name) */ name FROM test WHERE name = 'a'
> AND
> > description= 'b'
> > ColumnNotFoundException: ERROR 504 (42703): Undefined column.
> > columnName=DESCRIPTION.
> >
> > Thanks,
> > Alexey.
> >
> >
> >
> > --
> > Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
>
Re: Secondary index question
Posted by Jonathan Leech <jo...@gmail.com>.
I’ve done what you’re looking for by selecting the pk from the index in a nested query and filtering the other column separately.
> On Feb 27, 2018, at 6:39 AM, Alexey Karpov <ka...@yandex.ru> wrote:
>
> Thanks for quick answer, but my case is a slightly different. I've seen these
> links and already use local index. All cases, described in faq, index_usage
> and any other, I've found in this user list, are about SELECT clause. In
> WHERE clause there is always field from the index.
>
> In my case in WHERE clause I have one field from the index and one not from
> the index, combined with AND operator:
> SELECT * from test WHERE name = 'a' *AND description = 'b'*
> name - from the index
> description - not from the index
>
> Without filter on description (only on name) Phoenix uses index, as expected
> for local index. But with additional filter Phoenix decides to do a full
> scan. And my question is: Is there any way to make Phoenix use index in such
> types of queries, without include all fields in index ?
>
> Hint does not help:
> SELECT /*+ INDEX(test ix_test_name) */ name FROM test WHERE name = 'a' AND
> description= 'b'
> ColumnNotFoundException: ERROR 504 (42703): Undefined column.
> columnName=DESCRIPTION.
>
> Thanks,
> Alexey.
>
>
>
> --
> Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
Re: Secondary index question
Posted by Alexey Karpov <ka...@yandex.ru>.
Thanks for quick answer, but my case is a slightly different. I've seen these
links and already use local index. All cases, described in faq, index_usage
and any other, I've found in this user list, are about SELECT clause. In
WHERE clause there is always field from the index.
In my case in WHERE clause I have one field from the index and one not from
the index, combined with AND operator:
SELECT * from test WHERE name = 'a' *AND description = 'b'*
name - from the index
description - not from the index
Without filter on description (only on name) Phoenix uses index, as expected
for local index. But with additional filter Phoenix decides to do a full
scan. And my question is: Is there any way to make Phoenix use index in such
types of queries, without include all fields in index ?
Hint does not help:
SELECT /*+ INDEX(test ix_test_name) */ name FROM test WHERE name = 'a' AND
description= 'b'
ColumnNotFoundException: ERROR 504 (42703): Undefined column.
columnName=DESCRIPTION.
Thanks,
Alexey.
--
Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
Re: Secondary index question
Posted by Josh Elser <el...@apache.org>.
Thanks for the pointer, James. I looked quickly but didn't find it :)
Let us know if that explains it, Alexey.
On 2/26/18 1:41 PM, James Taylor wrote:
> 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 <elserj@apache.org
> <ma...@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/
> <http://apache-phoenix-user-list.1124778.n5.nabble.com/>
>
>
Re: Secondary index question
Posted by James Taylor <ja...@apache.org>.
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 <el...@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/
>>
>>
Re: Secondary index question
Posted by Josh Elser <el...@apache.org>.
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/
>