title: Should I increase sort_buffer_size in MySQL?
body: <p>I am running a site which extensively queries a MySQL DB. Some pages querying the DB server loads very slowly and I am pretty sure it is due to the DB server. Just by looking into the DB server usage, I can see that the iowait is >10 seconds, the swap space is also partially used. After turning on the slow-query-log, I found one query is consistently slow. The query looks like</p>
  
  <pre><code>select g.citing from g, c where g.cited=8992 and g.citing=c.id order by c.size desc limit 20;
  </code></pre>
  
  <p>Typical query time is about 10 seconds. The innodb_buffer_pool_size is set to 24GB. I read some MySQL manual pages, and found that I may need to tune the sort_buffer_size parameter because the <code>Sort_merge_passes</code> status variable is very high (about 310818). The current value of <code>sort_buffer_size</code> is 2M and I am not sure if I should go up or down because some people said never go beyond 2M. 
  The current query does not take advantage of index. The result of the "EXPLAIN" statement gives the following results:</p>
  
  <pre><code>+----+-------------+-----------+--------+---------------+---------+---------+--------------------------------+------+---------------------------------+
  | id | select_type | table     | type   | possible_keys | key     | key_len | ref                            | rows | Extra                           |
  +----+-------------+-----------+--------+---------------+---------+---------+--------------------------------+------+---------------------------------+
  |  1 | SIMPLE      | g         | ref    | citing,cited  | cited   | 8       | const                          |  306 | Using temporary; Using filesort |
  |  1 | SIMPLE      | c         | eq_ref | PRIMARY,id    | PRIMARY | 8       | db1.g.citing                    |    1 |                 |
  +----+-------------+-----------+--------+---------------+---------+---------+--------------------------------+------+---------------------------------+
  </code></pre>
  
  <p>I tried to consider a better query, which can take advantage of index, but no luck for now. Can anyone help me out? I appreciate it.</p>