How to evaluate the temporary disk space required for a MySQL query?

Question

I have a set of rather complex SELECT queries that use a lot of disk space (I see this from df -h at runtime). Is there a way to estimate the temporary disk space needed for a request before running it?

Answer 1

You can use the EXPLAIN keyword to describe how your joins will affect the number of rows that will be joined together. It will also help you use the keys correctly if they are not already present. The explanation will tell you when he thinks that he will need to use temporary tables (disk space). Based on the size of the connected lines, you can roughly estimate the amount of disk space.

See the documents given here:

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

Basically, just add “Explain” to your selected query to get an informational output. I believe that you can also do this programmatically, if necessary, and use the results in your actual code, say, for example, you had to calculate (estimate) a long query time and display it to the user before continuing.