Index hints

[Steve McLeod]

I'm considering attempting to add index hints to H2.

Questions: 

* Is this a good idea? Or is it  better to work on making the query optimizer smarter at choosing the correct index?

* Is Oracle's syntax preferable? Or MySQL's syntax? Or is there a better syntax altogether?

Oracle:

SELECT /*+ INDEX (employees emp_department_ix)*/ 

       employee_id, department_id 

  FROM employees 

  WHERE department_id > 50;

MySQL:

SELECT * FROM table1 USE INDEX (col1_index,col2_index)

  WHERE col1=1 AND col2=2 AND col3=3;

[Noel Grandin]

I'm ok with that. I think MySQL' s index syntax looks ok to me, although I'd prefer an index name instead of a key list.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[goran....@gmail.com]

Another option, if we want to be more complete when it comes to how things are executed…

Is to use Sybase/SAP syntax

select * from titles where title_id = 'On Liberty'

plan '(i_scan title_id_ix titles)'

 

The nice thing with the above syntax is that it’s

·         “detached” from the SQL statement itself (not embedded in the *actual* sql text, but appended after the statement)
Or decouples the SQL text from the execution plan

·         Easier to extend the syntax of the “plan” specification (we can have an alternate parser for parsing the execution plan)
Meaning not *just* index hints, but also join order, if we want to use nested loop join or merge join or... etc, etc…

·         Maybe over the top: later on at “execution time” an “abstract plan” can be associated with a specific SQL Statement/text
(this so the application doesn’t have to be changed just because you want to “change” the execution plan)

 

The “Abstract Query Plan” is described in: http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00743.1570/html/queryprocessing/X14674.htm

In there we might be able to get some ideas that can be reused in H2

 

/Göran Schwarz

--

[Sergi Vladykin]

I'd prefer to just go with MySQL syntax and not reinvent the wheel.

Sergi

2017-01-04 16:14 GMT+03:00 Noel Grandin <noelg...@gmail.com>:

I'm ok with that. I think MySQL' s index syntax looks ok to me, although I'd prefer an index name instead of a key list.

On Wed, 04 Jan 2017 at 14:13, Steve McLeod <steve....@gmail.com> wrote:

I'm considering attempting to add index hints to H2.

Questions: 

* Is this a good idea? Or is it  better to work on making the query optimizer smarter at choosing the correct index?

* Is Oracle's syntax preferable? Or MySQL's syntax? Or is there a better syntax altogether?

Oracle:

SELECT /*+ INDEX (employees emp_department_ix)*/ 

       employee_id, department_id 

  FROM employees 

  WHERE department_id > 50;

MySQL:

SELECT * FROM table1 USE INDEX (col1_index,col2_index)

  WHERE col1=1 AND col2=2 AND col3=3;

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller Graf]

Hi,

The MySQL syntax sounds good to me. As an alternative, the MS SQL Server syntax, see also https://msdn.microsoft.com/en-us/library/ms190322.aspx and maybe http://blog.sqlauthority.com/2009/02/08/sql-server-introduction-to-force-index-query-hints-index-hint-part2/

A related feature is to fail queries if no index is available. This is what SQLite is doing: https://sqlite.org/lang_indexedby.html . Apache Jackrabbit Oak has a similar feature: https://issues.apache.org/jira/browse/OAK-4888 

I'm not sure which feature is more important.

Regards,

Thomas

[Steve McLeod]

MySQL syntax seems to be the popular option. I'll use that.

Proposed Solution

==============

I'll add this to the H2 SQL syntax:

SELECT [TOP term] [DISTINCT | ALL] selectExpression FROM tableExpression [USE INDEX (indexList)] ...

where indexList is defined as :

indexName [, indexName]*

Each index in the list must exist, otherwise an INDEX_NOT_FOUND_1 (error code 42112) exception is thrown.

Only indexes in the list will be used when choosing an index to use on the given tableExpression. There is no significance to order in this list.

It is possible that no index in the list is chosen, in which case a full table scan will be used.

To test this, I'll add test cases that check that the result of EXPLAIN ANALYZE indicates that a specified index is used (or is not used).

> A related feature is to fail queries if no index is available.

This sounds like a great idea, but I won't do this as part of this change. With this enhancement I'm trying to solve one of my own performance issues.

Any feedback on this proposed solution?

[Thomas Mueller Graf]

Hi,

> With this enhancement I'm trying to solve one of my own performance issues.

Sure, it sounds good to me. The "fail query" is a different feature, the links were just FYI.

> It is possible that no index in the list is chosen, in which case a full table scan will be used.

It looks like this is what MySQL is doing, so that's fine.

Regards,

Thomas

[Steve McLeod]

Pull request submitted.

Surprisingly, while doing this I discovered we already accept Sybase's format for index hints, although the hints are ignored:

// Sybase compatibility with

// "select * from test (index table1_index)"

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.