mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 4537 Message: Variable ’collation_server’ cannot be set using SET_VAR hint. 

SET_VAR  syntax permits setting only a single variable, but multiple hints can be given to set multiple variables: 

SELECT /*+ SET_VAR(optimizer_switch = ’mrr_cost_based=off’) SET_VAR(max_heap_table_size = 1G) */ 1; 

If several hints with the same variable name appear in the same statement, the first one is applied and the others are ignored with a warning: 

SELECT /*+ SET_VAR(max_heap_table_size = 1G) SET_VAR(max_heap_table_size = 3G) */ 1; 

In this case, the second hint is ignored with a warning that it is conflicting. 

A SET_VAR  hint is ignored with a warning if no system variable has the specified name or the variable value is incorrect: 

SELECT /*+ SET_VAR(max_size = 1G) */ 1; SELECT /*+ SET_VAR(optimizer_switch = ’mrr_cost_based=yes’) */ 1; 

For the first statement, there is no max_size  variable. For the second statement, mrr_cost_based takes values of on  or off , so attempting to set it to yes  is incorrect. In each case, the hint is ignored with a warning. 

The SET_VAR  hint is permitted only at the statement level. If used in a subquery, the hint is ignored with a warning. 

Replicas ignore SET_VAR  hints in replicated statements to avoid the potential for security issues. 

## Resource Group Hint Syntax 

The RESOURCE_GROUP  optimizer hint is used for resource group management (see Section 5.1.16, “Resource Groups” ). This hint assigns the thread that executes a statement to the named resource group temporarily (for the duration of the statement). It requires the RESOURCE_GROUP_ADMIN  or RESOURCE_GROUP_USER  privilege. 

Examples: 

SELECT /*+ RESOURCE_GROUP(USR_default) */ name FROM people ORDER BY name; INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2); 

Syntax of the RESOURCE_GROUP  hint: 

RESOURCE_GROUP( group_name ) 

group_name  indicates the resource group to which the thread should be assigned for the duration of statement execution. If the group is nonexistent, a warning occurs and the hint is ignored. 

The RESOURCE_GROUP  hint must appear after the initial statement keyword ( SELECT , INSERT , REPLACE , UPDATE , or DELETE ). 

An alternative to RESOURCE_GROUP  is the SET RESOURCE GROUP  statement, which nontemporarily assigns threads to a resource group. See Section 13.7.2.4, “SET RESOURCE GROUP Statement” . 

