You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Sergi Vladykin <se...@gmail.com> on 2017/01/23 11:17:46 UTC
SQL: Index hints
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
Re: SQL: Index hints
Posted by Sergi Vladykin <se...@gmail.com>.
Exactly, this syntax was taken from MySQL.
Sergi
2017-04-21 9:58 GMT+03:00 Denis Magda <dm...@gridgain.com>:
> 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 <se...@gmail.com>
> 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 <dmagda@apache.org
> <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 <dmagda@apache.org
> > <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 <
> > dsetrakyan@apache.org <javascript:;>>
> > > wrote:
> > > >>
> > > >> Very cool! Would be nice to add it to Ignite.
> > > >>
> > > >> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <
> > > sergi.vladykin@gmail.com <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
> > > >>>
> > > >
> > >
> > >
> >
>
Re: SQL: Index hints
Posted by Denis Magda <dm...@gridgain.com>.
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 <se...@gmail.com>
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 <dmagda@apache.org <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 <dmagda@apache.org
> <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 <
> dsetrakyan@apache.org <javascript:;>>
> > wrote:
> > >>
> > >> Very cool! Would be nice to add it to Ignite.
> > >>
> > >> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <
> > sergi.vladykin@gmail.com <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
> > >>>
> > >
> >
> >
>
Re: SQL: Index hints
Posted by Sergi Vladykin <se...@gmail.com>.
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 <dm...@apache.org>:
> 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 <dm...@apache.org> 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 <ds...@apache.org>
> wrote:
> >>
> >> Very cool! Would be nice to add it to Ignite.
> >>
> >> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <
> sergi.vladykin@gmail.com>
> >> 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
> >>>
> >
>
>
Re: SQL: Index hints
Posted by Denis Magda <dm...@apache.org>.
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 <dm...@apache.org> 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 <ds...@apache.org> wrote:
>>
>> Very cool! Would be nice to add it to Ignite.
>>
>> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <se...@gmail.com>
>> 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
>>>
>
Re: SQL: Index hints
Posted by Denis Magda <dm...@apache.org>.
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 <ds...@apache.org> wrote:
>
> Very cool! Would be nice to add it to Ignite.
>
> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <se...@gmail.com>
> 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
>>
Re: SQL: Index hints
Posted by Dmitriy Setrakyan <ds...@apache.org>.
Very cool! Would be nice to add it to Ignite.
On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <se...@gmail.com>
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
>