MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
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!