--------

One can limit statement execution time max_statement_time:

SET STATEMENT max_statement_time=1000 FOR SELECT ... ;

One can switch on/off individual optimizations:

SET STATEMENT optimizer_switch='materialization=off' FOR SELECT ....;

It is possible to enable MRR/BKA for a query:

SET STATEMENT  join_cache_level=6, optimizer_switch='mrr=on'  FOR SELECT ...

Note that it makes no sense to try to set a session variable inside a SET
STATEMENT:

#USELESS STATEMENT
SET STATEMENT sort_buffer_size = 100000 for SET SESSION sort_buffer_size =
200000;

For the above, after setting sort_buffer_size to 200000 it will be reset to
its original state (the state before the SET STATEMENT started) after the
statement execution.

Limitations
-----------

There are a number of variables that cannot be set on per-query basis. These
include:

* autocommit
* character_set_client
* character_set_connection
* character_set_filesystem
* collation_connection
* default_master_connection
* debug_sync
* interactive_timeout
* gtid_domain_id
* last_insert_id
* log_slow_filter
* log_slow_rate_limit
* log_slow_verbosity
* long_query_time
* min_examined_row_limit
* profiling
* profiling_history_size
* query_cache_type
* rand_seed1
* rand_seed2
* skip_replication
* slow_query_log
* sql_log_off
* tx_isolation
* wait_timeout

Source
------

* The feature was originally implemented as a Google Summer of Code 2009
project by Joseph Lukas. 
* Percona Server 5.6 included it as Per-query variable statement
* MariaDB ported the patch and fixed many bugs. The task in MariaDB Jira is
MDEV-5231.

URL: https://mariadb.com/kb/en/set-statement/https://mariadb.com/kb/en/set-statement/