If your queries are constantly writing to disk, and you cannot fix your query (which is strongly recommended) or properly adjust your buffer sizes for one reason or another, you can create a memory backed file system for the tmpdir, which should allow for incredibly faster writes and reads.
This should only be viewed as a temporary fix until you can get your query to stop writing its temp tables to disk.
Create the mount point:
mkdir -p /tmp/mysqltmp
Now give MySQL access to it:
chown mysql:mysql /tmp/mysqltmp
Set up the entry in /etc/fstab so it will mount on reboot:
* NOTE: Change the uid and gid to whatever MySQL runs as in /etc/passwd and /etc/group
vi /etc/fstab # Add tmpfs /tmp/mysqltmp tmpfs rw,gid=27,uid=27,size=256M,nr_inodes=10k,mode=0700 0 0
Mount the memory backed file system:
mount -a
Update /etc/my.cnf and add the variable:
tmpdir = /tmp/mysqltmp
Now, restart MySQL:
service mysql restart
Finally, confirm MySQL is now utilizing the new memory backed filesystem:
mysql mysql> show variables like '%tmpdir%'; +-------------------+----------------+ | Variable_name | Value | +-------------------+----------------+ | slave_load_tmpdir | /tmp | | tmpdir | /tmp/mysqltmp | +-------------------+----------------+ 2 rows in set (0.00 sec)