Description:
Setting sort_buffer_size to be fairly large (e.g 64M) slows down sorts even when the size of the data to be sorted is exceedingly small. This only seems to occur when the source table is large, regardless of the number of rows actually being sorted.
How to repeat:
mysql> select count(*) from Song;
+----------+
| count(*) |
+----------+
| >1M ROWS BIG TABLE HERE [hidden] |
+----------+
1 row in set (0.14 sec)
mysql> optimize table Song;
+------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------+----------+----------+----------+
| amiest_production.Song | optimize | status | OK |
+------------------------+----------+----------+----------+
1 row in set (59.99 sec)
mysql> select SQL_NO_CACHE id from Song where album_id = 1805 order by amie_index desc;
+------+
| id |
+------+
| 9820 |
| 9816 |
| 9818 |
| 9822 |
| 9817 |
| 9821 |
| 9819 |
| 9823 |
| 9813 |
| 9815 |
| 9814 |
| 9812 |
+------+
12 rows in set (0.01 sec) <--- consistently 0.01 or 0.02sec
mysql> create table test like Song;
Query OK, 0 rows affected (0.10 sec)
mysql> explain select SQL_NO_CACHE id from Song where album_id = 1805 order by amie_index desc;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | Song | ref | album_id | album_id | 4 | const | 12 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> insert into test select * from Song where id < 10000;
mysql> select SQL_NO_CACHE id from test where album_id = 1805 order by amie_index desc;
+------+
| id |
+------+
| 9820 |
| 9816 |
| 9818 |
| 9822 |
| 9817 |
| 9821 |
| 9819 |
| 9823 |
| 9813 |
| 9815 |
| 9814 |
| 9812 |
+------+
12 rows in set (0.00 sec) <-- consistently 0.00 sec
Using the community SHOW PROFILE patch shows the extra time is all occurring inside filesort.
After SET SESSION sort_buffer_size = 256*1024; all queries take <10ms consistently.
Tested on 5.0.62, 5.0.67, 5.0.68 from bzr.
Suggested fix:
sort_buffer_size should be a maximum - if the amount of data being sorted is less than sort_buffer_size, then increasing sort_buffer_size should have no effect.
