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]. 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.
