[MDEV-26485] Add flexible optimizer hints support for every setting in the optimizer_switch and more Created: 2021-08-27  Updated: 2023-12-18

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: 11.6

Type: New Feature Priority: Major
Reporter: Valerii Kravchuk Assignee: Michael Widenius
Resolution: Unresolved Votes: 0
Labels: None


 Description   

MySQL 8 implemented hints that can be embedded into the query text (for example, in the view definition) and used to control every optimizer option available in the optimizer_switch for every table (among other hints). See https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html for more details.

I think we should do the same, as finding and fixing every place where SET STATEMENT ... FOR may help to get good plan for some problematic view is too complex to be practical. This was already discussed as a large project in MDEV-12980.

 Comments   
Comment by Valerii Kravchuk [ 2022-03-27 ]

I wonder why it takes so much time to make a decision on this task?

Comment by Sergei Petrunia [ 2022-06-01 ]

In MySQL:

Hints use this syntax /*+ hint_text */

Hints are located at the beginning of query blocks or data statements:

SELECT /*+ hint */ ...
 
UPDATE /*+ hint */ ...

There is a separate parser for hints. The common syntax is 

hint_name(args)

the arguments depend on the hint.

Comment by Sergei Petrunia [ 2022-06-02 ]

Notes from the optimizer call (most of the points by Monty)

  • We would like to have optimizer hints.
  • MySQL's approach to hint syntax is generally fine
  • Some of MySQL's hints make sense (e.g. SUBQUERY, , some seem unnecessary (e.g. NO_ICP)
  • Some of MySQL's hints are already implemented in non-hint way in MariaDB (e.g. SET STATEMENT implements SET_VAR hint)
  • However this syntax is different from the current hints (e.g. FORCE INDEX). Having to use two different kinds of syntax at the same time is inconvenient. We need to make sure it's possible to use only the new form, that is, the new syntax should allow everything that the old syntax did.
  • Also add a version# check, similar to how it works for comments: /*+version hint */
Comment by Sergei Petrunia [ 2023-06-27 ]

ralf.gebhardt, I think this is 2 Dev Sprints.

Generated at Thu Feb 08 09:45:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.