SQL: Index hints

[Sergi]

Guys,

Recently in H2 we've merged a very important feature: index hints. It is an
additional MySQL-like syntax:

SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1

It will be very easy to support this in Ignite.

Alex,

Since you are working on better SQL Enum support and it will require H2
upgrade anyways, you can add this stuff to Ignite as well.

Sergi

[dsetrakyan]

Very cool! Would be nice to add it to Ignite.

On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <[hidden email]>
wrote:

> Guys,
>
> Recently in H2 we've merged a very important feature: index hints. It is an
> additional MySQL-like syntax:
>
> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
>
> It will be very easy to support this in Ignite.
>
> Alex,
>
> Since you are working on better SQL Enum support and it will require H2
> upgrade anyways, you can add this stuff to Ignite as well.
>
> Sergi
>

[dmagda]

Created a ticket so that we don’t forget about this new H2 capability.
https://issues.apache.org/jira/browse/IGNITE-4594 <https://issues.apache.org/jira/browse/IGNITE-4594>

Alexander P. feel free to assign it on yourself.

—
Denis

> On Jan 23, 2017, at 10:05 AM, Dmitriy Setrakyan <[hidden email]> wrote:
>
> Very cool! Would be nice to add it to Ignite.
>
> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <[hidden email]>
> wrote:
>
>> Guys,
>>
>> Recently in H2 we've merged a very important feature: index hints. It is an
>> additional MySQL-like syntax:
>>
>> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
>>
>> It will be very easy to support this in Ignite.
>>
>> Alex,
>>
>> Since you are working on better SQL Enum support and it will require H2
>> upgrade anyways, you can add this stuff to Ignite as well.
>>
>> Sergi
>>

[dmagda]

Sergi, I’ve documented this feature for 2.0. Please confirm that the text below is technically correct:

Index hints are useful in scenarios when it's known that one index is more selective for certain queries than another and it's needed to instruct the query optimizer to choose a more efficient execution plan. To do this trick in Apache Ignite use USE_INDEX(index_list) statement that tells Ignite to take only one of the named indexes for query execution.

Below is an example that leverages from this capability:

SELECT * FROM table1 USE_INDEX(index_age)
  WHERE salary > 150000 AND age < 35;

—
Denis

> On Jan 23, 2017, at 12:19 PM, Denis Magda <[hidden email]> wrote:
>
> Created a ticket so that we don’t forget about this new H2 capability.
> https://issues.apache.org/jira/browse/IGNITE-4594 <https://issues.apache.org/jira/browse/IGNITE-4594>
>
> Alexander P. feel free to assign it on yourself.
>
> —
> Denis
>
>> On Jan 23, 2017, at 10:05 AM, Dmitriy Setrakyan <[hidden email]> wrote:
>>
>> Very cool! Would be nice to add it to Ignite.
>>
>> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <[hidden email]>
>> wrote:
>>
>>> Guys,
>>>
>>> Recently in H2 we've merged a very important feature: index hints. It is an
>>> additional MySQL-like syntax:
>>>
>>> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
>>>
>>> It will be very easy to support this in Ignite.
>>>
>>> Alex,
>>>
>>> Since you are working on better SQL Enum support and it will require H2
>>> upgrade anyways, you can add this stuff to Ignite as well.
>>>
>>> Sergi
>>>
>

[Sergi]

No, it must be USE INDEX without underscore. Also mention that multiple
indexes can be listed.

http://h2database.com/html/grammar.html#table_expression

Sergi


2017-04-21 4:25 GMT+03:00 Denis Magda <[hidden email]>:

> Sergi, I’ve documented this feature for 2.0. Please confirm that the text
> below is technically correct:
>
> Index hints are useful in scenarios when it's known that one index is more
> selective for certain queries than another and it's needed to instruct the
> query optimizer to choose a more efficient execution plan. To do this trick
> in Apache Ignite use USE_INDEX(index_list) statement that tells Ignite to
> take only one of the named indexes for query execution.
>
> Below is an example that leverages from this capability:
>
> SELECT * FROM table1 USE_INDEX(index_age)
>   WHERE salary > 150000 AND age < 35;
>
> —
> Denis
>
> > On Jan 23, 2017, at 12:19 PM, Denis Magda <[hidden email]> wrote:
> >
> > Created a ticket so that we don’t forget about this new H2 capability.
> > https://issues.apache.org/jira/browse/IGNITE-4594 <
> https://issues.apache.org/jira/browse/IGNITE-4594>
> >
> > Alexander P. feel free to assign it on yourself.
> >
> > —
> > Denis
> >
> >> On Jan 23, 2017, at 10:05 AM, Dmitriy Setrakyan <[hidden email]>
> wrote:
> >>
> >> Very cool! Would be nice to add it to Ignite.
> >>
> >> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <
> [hidden email]>
> >> wrote:
> >>
> >>> Guys,
> >>>
> >>> Recently in H2 we've merged a very important feature: index hints. It
> is an
> >>> additional MySQL-like syntax:
> >>>
> >>> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
> >>>
> >>> It will be very easy to support this in Ignite.
> >>>
> >>> Alex,
> >>>
> >>> Since you are working on better SQL Enum support and it will require H2
> >>> upgrade anyways, you can add this stuff to Ignite as well.
> >>>
> >>> Sergi
> >>>
> >
>
>

[Denis Magda]

If multiple indexes are listed then H2 will pick only one of them like
MySql does, right?

Denis

On Thursday, April 20, 2017, Sergi Vladykin <[hidden email]>
wrote:

> No, it must be USE INDEX without underscore. Also mention that multiple
> indexes can be listed.
>
> http://h2database.com/html/grammar.html#table_expression
>
> Sergi
>
>
> 2017-04-21 4:25 GMT+03:00 Denis Magda <[hidden email] <javascript:;>>:
>
> > Sergi, I’ve documented this feature for 2.0. Please confirm that the text
> > below is technically correct:
> >
> > Index hints are useful in scenarios when it's known that one index is
> more
> > selective for certain queries than another and it's needed to instruct
> the
> > query optimizer to choose a more efficient execution plan. To do this
> trick
> > in Apache Ignite use USE_INDEX(index_list) statement that tells Ignite to
> > take only one of the named indexes for query execution.
> >
> > Below is an example that leverages from this capability:
> >
> > SELECT * FROM table1 USE_INDEX(index_age)
> >   WHERE salary > 150000 AND age < 35;
> >
> > —
> > Denis
> >
> > > On Jan 23, 2017, at 12:19 PM, Denis Magda <[hidden email]
> <javascript:;>> wrote:
> > >
> > > Created a ticket so that we don’t forget about this new H2 capability.
> > > https://issues.apache.org/jira/browse/IGNITE-4594 <
> > https://issues.apache.org/jira/browse/IGNITE-4594>
> > >
> > > Alexander P. feel free to assign it on yourself.
> > >
> > > —
> > > Denis
> > >
> > >> On Jan 23, 2017, at 10:05 AM, Dmitriy Setrakyan <
> [hidden email] <javascript:;>>
> > wrote:
> > >>
> > >> Very cool! Would be nice to add it to Ignite.
> > >>
> > >> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <
> > [hidden email] <javascript:;>>
> > >> wrote:
> > >>
> > >>> Guys,
> > >>>
> > >>> Recently in H2 we've merged a very important feature: index hints. It
> > is an
> > >>> additional MySQL-like syntax:
> > >>>
> > >>> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
> > >>>
> > >>> It will be very easy to support this in Ignite.
> > >>>
> > >>> Alex,
> > >>>
> > >>> Since you are working on better SQL Enum support and it will require
> H2
> > >>> upgrade anyways, you can add this stuff to Ignite as well.
> > >>>
> > >>> Sergi
> > >>>
> > >
> >
> >
>

[Sergi]

Exactly, this syntax was taken from MySQL.

Sergi

2017-04-21 9:58 GMT+03:00 Denis Magda <[hidden email]>:

> If multiple indexes are listed then H2 will pick only one of them like
> MySql does, right?
>
> Denis
>
> On Thursday, April 20, 2017, Sergi Vladykin <[hidden email]>
> wrote:
>
> > No, it must be USE INDEX without underscore. Also mention that multiple
> > indexes can be listed.
> >
> > http://h2database.com/html/grammar.html#table_expression
> >
> > Sergi
> >
> >
> > 2017-04-21 4:25 GMT+03:00 Denis Magda <[hidden email]
> <javascript:;>>:
> >
> > > Sergi, I’ve documented this feature for 2.0. Please confirm that the
> text
> > > below is technically correct:
> > >
> > > Index hints are useful in scenarios when it's known that one index is
> > more
> > > selective for certain queries than another and it's needed to instruct
> > the
> > > query optimizer to choose a more efficient execution plan. To do this
> > trick
> > > in Apache Ignite use USE_INDEX(index_list) statement that tells Ignite
> to
> > > take only one of the named indexes for query execution.
> > >
> > > Below is an example that leverages from this capability:
> > >
> > > SELECT * FROM table1 USE_INDEX(index_age)
> > >   WHERE salary > 150000 AND age < 35;
> > >
> > > —
> > > Denis
> > >
> > > > On Jan 23, 2017, at 12:19 PM, Denis Magda <[hidden email]
> > <javascript:;>> wrote:
> > > >
> > > > Created a ticket so that we don’t forget about this new H2
> capability.
> > > > https://issues.apache.org/jira/browse/IGNITE-4594 <
> > > https://issues.apache.org/jira/browse/IGNITE-4594>
> > > >
> > > > Alexander P. feel free to assign it on yourself.
> > > >
> > > > —
> > > > Denis
> > > >
> > > >> On Jan 23, 2017, at 10:05 AM, Dmitriy Setrakyan <
> > [hidden email] <javascript:;>>
> > > wrote:
> > > >>
> > > >> Very cool! Would be nice to add it to Ignite.
> > > >>
> > > >> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <
> > > [hidden email] <javascript:;>>
> > > >> wrote:
> > > >>
> > > >>> Guys,
> > > >>>
> > > >>> Recently in H2 we've merged a very important feature: index hints.
> It
> > > is an
> > > >>> additional MySQL-like syntax:
> > > >>>
> > > >>> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
> > > >>>
> > > >>> It will be very easy to support this in Ignite.
> > > >>>
> > > >>> Alex,
> > > >>>
> > > >>> Since you are working on better SQL Enum support and it will
> require
> > H2
> > > >>> upgrade anyways, you can add this stuff to Ignite as well.
> > > >>>
> > > >>> Sergi
> > > >>>
> > > >
> > >
> > >
> >
>