Some of the variable settings has much more meaning being set for query rather than for connection. That is you might with to increase sort_buffer_size before doing large sort query but it is very likely other queries in a session are simple and quite OK with default settings. Now to one can set variable value on query basics the following way: 1) store current value got by SHOW SESSION VARIABLES LIKE "name"; 2) set new variable value 3) run the query. 4) restore old settings Which require 4 steps instead of one, which can override all benefit of tuning, also it requires changing the application rather than the query itself. The good solution is for queries which make sense in query contexts such as record_buffer, record_rnd_buffer, join_buffer, sort_buffer, tmp_table_size allow setting them inside of the query: 1. SELECT MYSQL_SET_VARIABLE sort_buffer=16M name FROM people ORDER BY name; or (sh style) 2. @@sort_buffer=16M SELECT name FROM people ORDER BY name; or 3. SET STATEMENT @@sort_buffer=16M SELECT name FROM people ORDER BY name; and SET @@statement.sort_buffer=16M SELECT name FROM people ORDER BY name; or even 4. SET STATEMENT @@sort_buffer=16M; SELECT name FROM people ORDER BY name; and SET @@statement.sort_buffer=16M; SELECT name FROM people ORDER BY name; We'll do #3.
The syntax will be SET STATEMENT ... FOR <statement> "FOR" was added to help the parser (read - resolve reduce/reduce conflicts) to separate the expression in the assignment from a statement, as (SELECT 1) is both a valid <statement> and a valid <expression>. SET STATEMENT ... FOR <statement> can be used anywhere a <statement> can be. the feature is implemented by remembering a list of modified variables and their old values at the beginning of the mysql_execute_command, and restoring the value at the end. An error in assignment aborts the statement, an error in restoring the value is converted to a warning (too late to abort).
this was a GSoC 2009 project original tree is here - lp:~jlukas79/+junk/mysql-server/ our tree with post-review fixes is here - my:6.0-wl681