[jira] [Created] (CALCITE-5258) Support MySQL style index hints

["Dirk (Jira)" <ji...@apache.org>]

Dirk created CALCITE-5258:
-----------------------------

             Summary: Support MySQL style index hints
                 Key: CALCITE-5258
                 URL: https://issues.apache.org/jira/browse/CALCITE-5258
             Project: Calcite
          Issue Type: Improvement
         Environment: Calcite 1.31.0
            Reporter: Dirk


{{MySQL has its own syntax for index hints: [https://dev.mysql.com/doc/refman/8.0/en/index-hints.html]}}

{{Some examples are :}}
{{SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;}}
{{SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);}}

{{This differs from the index hints syntax that Calcite supports and that most DBMSes support. }}

{{From my tests MySQL does not support the /+ ... */ syntax of hints. when running queries like this all are successfully executed only 1 has an index choice effect.}}
{{1) SELECT * FROM mytable USE INDEX(col1_index)  WHERE col2= false AND col1=3 AND col3= '1'}}
{{2) SELECT * from mytable /*+ INDEX(col1_index) */ WHERE col2= false AND col1=3 AND col3= '1'}}
{{3) SELECT /*+ INDEX(col1_index) */ * FROM mytable WHERE col2= false AND col1=3 AND col3= '1'}}

{{It would be great if this would be supported, because otherwise index hints cannot be used with MySQL.}}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)