Have you ever looked at your processlist and seen queries in the state “copying to tmp table” and then run an explain on it and noticed that the tmp table is being created on disk? Happens a lot with some servers and some workloads. Of course disk is much slower than RAM so this becomes a slow process and makes queries execute slower than they could if they were allowed to use RAM. So, one way to get this process to speed up (aside from tuning your queries which should be done first) is to create a tmpfs or ram-disk and let MySQL use that for it’s temp-table-on-disk creations. MySQL on Linux defaults to /tmp for the tmpdir location so this will need to be changed.

Here is how you get MySQL to use a 1G size tmpfs. How you size your tmpfs depends how much ram your system has and how much tmpdir space mysql needs for your workload. If you need more tmpdir space than you can make in a tmpfs mount then mysql allows multiple tmpdir locations to be set (see the manual: http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_tmpdir).

0. mkdir -p /db/tmpfs01 && chown mysql:mysql /db/tmpfs01
1. mount tmpfs /db/tmpfs01 -t tmpfs -o size=1G
3. edit my.cnf file and add: tmpdir = /db/tmpfs01
4. restart MySQL

To have persistent tmpfs with reboot put the following into /etc/fstab. On this system the mysql user is uid/gid of 27, yours might vary so adjust accordingly.
tmpfs /db/tmpfs01 tmpfs rw,uid=27,gid=27,size=1G,nr_inodes=10k,mode=0755 0 0