I've got a query that's stalling, and I have no idea why. It looks sensible, but it runs "forever" (I went and had my lunch and left it running, hadn't finished 20 mins later).
Looking at "SHOW PROCESSLIST", I see my connection has the status "Copying to tmp table".
I looked in my my.cnf, and it looks like temp files should be in /tmp, but there's nothing relating to MySQL in there (there's only 2 directories, both empty).
To help me diagnose what was wrong, I thought I'd have a look at the temporary file it was supposedly copying to. If it was huge, I must have messed up a query. If not, perhaps it's a bug.
But I can't figure out where it's storing them. Is it an in-memory "file"?
I'm running MySQL 5.1.31 on a Ubuntu 9.04 JeOS install.
Best Answer
Many temporary tables can and will be created in-memory. Whether a table will be created in-memory or on-disk is determined by a few factors like size of the table, structure (Does it have BLOBs) and a few more.
I suggest you read up on TMP_TABLE_SIZE and MAX_HEAP_TABL\E_SIZE to gain insight on how temporary tables work.
You can check the output of
if you are actually creating on-disk tables. Note, however, that the actual temporary table file will be of little to no help in determining if there is a problem.
You could also try to EXPLAIN your query and see how well it should perform.