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)" On Thursday, 5 January 2017 10:30:03 UTC+1, Thomas Mueller Graf wrote: > > 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 > > > On Thu, Jan 5, 2017 at 9:48 AM, Steve McLeod <[email protected] > <javascript:>> wrote: > >> 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? >> >> >> >> On Wednesday, 4 January 2017 13:13:15 UTC+1, Steve McLeod 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 [email protected] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
