You might get a MySQL error like this:
ERROR 126 (HY000) at line 3: Incorrect key file for table '/var/tmp/#sql3f5_1b6c4e_1.MYI'; try to repair it
This probably means that you ran out of disk space on /var/tmp while MySQL was trying to create a temporary table.
The generation of temporary tables can be caused by derived tables (like a subselect) or filesort kicking in when you use ORDER BY.
What you can do to fix this:
- Put temporary tables on a bigger filesystem. Add to my.cnf:
[mysqld] tmpdir=/path/to/large/filesystem/mysql-tmp-dir
- Don't use a subselect. Rephrase it as a join. Use
SELECT STRAIGHT_JOINif you need to override the query optimizer to get your performance back. - Don't sort, or sort a smaller dataset if possible. For example:
SELECT id, huge_text_field FROM table ORDER BY some_numeric_field
Might run faster if you rephrase it as:
SELECT id, huge_text_field FROM table JOIN (SELECT id FROM table ORDER BY some_numeric_field ) AS derived_table USING (id)In the first example, filesort will operate over all the selected data (meaning: including the text, which is huge and doesn't affect the sort order). In the second example, the temporary table will only contain the id column, and the outer query will fetch the text chunks.
I wrote this up in May 2010, it was applicable to MySQL 5.1.