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!