New optimizer hint for changing the session system variable.

In MySQL 8.0.3, we have introduced a new optimizer hint called SET_VAR. This hint allows to change the value of a system variable for a single query.

Earlier, changing a system variable for a single query would require four steps:

Now, with SET_VAR hint, variable value can be changed in a one-shot way:

The SET_VAR hint enables setting the session value of a system variable for the duration of a single statement. The value specified in the hint is set before statement execution and restored after the execution. Thus the use of SET_VAR hint decreases the number of steps necessary to change variable value.

It’s also possible to use several SET_VAR hints at the same time:

Note that not all system variables are supported for the use with SET_VAR hint. We expect to add support for more variables(various storage engine related variables, for instance).

Current list of supported variables:

 

Thanks for using MySQL!

 

4 thoughts on “New optimizer hint for changing the session system variable.

    1. No, it can only be set for the entire statement. Subquery execution is often interleaved with the execution of the main query. Hence, it would require that one maintain separate settings for each query block. The way it is now, the variables are set at the beginning of the statement and reset when the statement is over.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.