MySQL Session variables and Hints

MySQL Session variables and Hints

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Share this post

Comments (7)

  • Kevin Burton Reply

    Yeah….. AND for REPAIR ALTER commands….

    KEvin

    December 29, 2006 at 11:44 pm
  • peter Reply

    Oh yes. Hints could be used for all sorts of commands.

    December 30, 2006 at 1:49 am
  • Güzin Başcı Reply

    thanks for article. i use it some project

    October 3, 2018 at 5:04 pm
  • Anaşehir Koleji Reply

    i use it my project las time

    October 3, 2018 at 5:17 pm
  • Serdar Yüce Reply

    thanks for this article.

    October 3, 2018 at 5:19 pm
  • Zelal Keleş Reply

    thanks for this article i use it my website

    October 3, 2018 at 5:21 pm
  • Oğuzhan Hoca Reply

    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.

    October 3, 2018 at 5:22 pm

Leave a Reply