MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query is executed for example
SELECT STRAIGHT_JOIN * FROM A FORCE INDEX(A) JOIN B
The other part is session variable. If you know query is going to require large sort you can do
SET sort_buffer_size=50000000 before executing query and
SET sort_buffer_size=DEFAULT after executing the query.
I noticed in production hints are used much more frequently than setting session variables for given query execution even though it also can be quite helpful and I believe the problem is it is more complicated. It also requires more round trips to the server but it is not that critical as it is complicated queries which normally need special values.
Especially if you’re patching third party application it is much easier to simply change the query (especially if it is not created dynamically some complex way) - adding extra MySQL calls with possibly their own error control is more complicated. Many people even dislike doing it in their own code.
The great solution I think would be to allow per session variables to be changed only for single query execution and returned to their old values after query is executed, so I could do something like:
SELECT SQL_SORT_BUFFER_SIZE=50000000 NAME FROM LARGE_TABLE ORDER BY NAME DESC LIMIT 10
This would also make it much easier to track - settings would show up in the query log (ie slow queries log) and SHOW PROCESSLIST so once could take it as a single query and profile rather than figuring our what custom session based settings it could have.
MySQL Session variables and Hints - Read More...
