How to estimate temporary disk space required for MySQL query?

Question

I have a set of quite complex SELECT queries which use a lot of disk space (I see this from df -h while running). Is there a way to estimate the temporary disk space required for a query before starting it?

Answer 1

You can use the EXPLAIN keyword to describe how your joins will effect the number of rows that will be joined together. This will also assist you in properly using keys if they are not already present. Explain will tell you when it thinks it will need to use temp tables (disk space). Based on the size of the rows being joined, you can then roughly estimate your disk space need.

See the docs on explain here:

http://dev.mysql.com/doc/refman/5.0/en/explain.html

Basically though, just prepend "Explain" to your select query to get info output. I believe you can also do this programatically if needed and use the results in your actual code, say for instance you needed to calculate(estimate) a large query run time and display it to the user before proceeding.