First, a bit of background information… MySQL has a variable called tmp_table_size which is used whenever a temporary table is needed for many types of operations, but it’s mainly used for ORDER BY and GROUP BY. The tmp_table_size variable (in correlation with the max_heap_table_size variable) decide at what point to convert an in-memory (that is, HEAP storage engine) temporary table to an on-disk (that is, MyISAM) one.

So, if the result of your query will end up greater than the lesser of tmp_table_size or max_heap_table_size, once it reaches that point, it will be automatically converted to a MyISAM table, on-disk (in tmpdir, incidentally).

Here’s a scenario: You have a SELECT query, nothing particularly special. It isn’t any more complex than another query on the same tables, but it performs a bit worse. You put some load on it, and it performs miserably, even putting the server in pain in the process. You check what’s happening… hmm, lots of blocks being written? Why would there be any blocks written, this is a SELECT!

Does the SELECT have a BLOB or TEXT column in it? Are you using a GROUP BY or ORDER BY that can’t use the same index that MySQL is choosing? (Check EXPLAIN for Using temporary or Using filesort.)

In that case, MySQL will need to create a temporary table to do the sort. Since your query contains a BLOB or TEXT column, the temporary table will have to be created as a MyISAM table, as the HEAP storage engine doesn’t support BLOB or TEXT (or even VARCHAR, for that matter, which is changed to CHAR automatically).

This will mean a lot of extra writes to the disk, for no good reason. What can you do about it?

Short term, for 4.1+: Abuse a subquery in the FROM to read the primary key of the table with the BLOB/TEXT and do the sorting that is necessary. In the outer query, select the full row, with BLOB/TEXT and return it, but don’t use ORDER BY.

Long term: Support for BLOB and TEXT need to be added to the HEAP storage engine.

What if you need to ORDER BY or GROUP BY the BLOB or TEXT column? Well, you’re screwed. Think about redesigning your database. :) (Hint: Add a column containing the first up to 255 bytes of the column as a VARCHAR and sort on that instead…)

Good luck!

Hacking the latest storage engine? Working on a fancy new SHOW command? Hunting for a bug in mysqld? This is for you!

Once you’ve got MySQL compiled, how do you test your mysqld binary? You’ll need to get it started. Stop! Don’t make install it!

1. Set up your sandbox

   $ mkdir ~/t
   $ vi ~/t/my.cnf
   

   Add the following to ~/t/my.cnf (modify to taste):

   [mysqld]
   basedir=.
   datadir=/Users/jcole/t 
   port=3307
   socket=/Users/jcole/t/mysql.sock
   skip-grant
   skip-innodb
   skip-bdb
   
   [client]
   port=3307
   socket=/Users/jcole/t/mysql.sock
   

2. Build your source
3. Set up a symlink for share/mysql pointing to the share directory.

   $ mkdir share && ln -s ../sql/share share/mysql
   

   This, combined with the basedir=. in my.cnf means that MySQL will be able to find its error message and character set files, so that the release you’re building doesn’t have to match the release installed on the system.

4. Start mysqld

   $ sql/mysqld --defaults-file=~/t/my.cnf
   

5. Start a mysql client (in another window)

   $ client/mysql --defaults-file=~/t/my.cnf
   

6. Shutdown the mysqld (if it didn’t crash :) )

   $ client/mysqladmin --defaults-file=~/t/my.cnf shutdown
   

That’s it! Much easier, safer, and faster than using make install, and you don’t need to remember all the options to specify to start and test your newly built mysqld! If you manage to break your sandbox, just delete everything except my.cnf and you are back to a clean slate.