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:
|
1 2 3 4 5 6 7 8 9 10 11 |
1) Store current value. SET @old_optimizer_switch = @@optimizer_switch; 2) Set new variable value. SET optimizer_switch = 'mrr_cost_based=off'; 3) Run the query. SELECT * FROM t1; 4) Restore old settings. SET optimizer_switch = @old_optimizer_switch; |
Now, with SET_VAR hint, variable value can be changed in a one-shot way:
|
1 2 3 |
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ * FROM t1; |
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:
|
1 2 3 4 |
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') SET_VAR(max_heap_table_size = 1G) */ * FROM t1; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
auto_increment_increment auto_increment_offset big_tables bulk_insert_buffer_size default_tmp_storage_engine div_precision_increment end_markers_in_json eq_range_index_dive_limit foreign_key_checks group_concat_max_len insert_id internal_tmp_mem_storage_engine join_buffer_size lock_wait_timeout max_error_count max_execution_time max_heap_table_size max_join_size max_length_for_sort_data max_points_in_geometry max_seeks_for_key max_sort_length optimizer_prune_level optimizer_search_depth variables optimizer_switch range_alloc_block_size range_optimizer_max_mem_size read_buffer_size read_rnd_buffer_size sort_buffer_size sql_auto_is_null sql_big_selects sql_buffer_result sql_mode sql_safe_updates sql_select_limit timestamp tmp_table_size updatable_views_with_limit unique_checks windowing_use_high_precision |
Thanks for using MySQL!
“..for the duration of a single statement”. What about subqueries?
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.