You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Denis Magda <dm...@apache.org> on 2017/04/21 01:25:57 UTC
Re: SQL: Index hints
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 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
> >>>
> >
>
>