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.

9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Kevin Burton

Yeah….. AND for REPAIR ALTER commands….

KEvin

0
Peter Zaitsev

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

0
Güzin Başcı

thanks for article. i use it some project

0
Anaşehir Koleji

i use it my project las time

0
Serdar Yüce

thanks for this article.

0
Zelal Keleş

thanks for this article i use it my website

0
Oğuzhan Hoca

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.

0
Göz Kapağı Estetiği

useful article thanks!

0
charpa.ir

Thanks great info had a great point of view.

0