Get total memory usage for constructing a result set from a query

Question

Is there a way to tell how much total memory MySQL used to construct a result set from a single query? What I'm looking for is something similar to php's memory_get_peak_usage().

I have a complex query that produces a few rows of summed data. The actual result set is tiny -- 6 or 8 rows of a floating decimal value. But the query I use to generate those values is rather complex -- it uses sums, grouping, subqueries, etc. over a large data set.

People have suggested using EXPLAIN and then estimating rows size from there. That sounds error-prone to me; I'm not sure I could figure it out anyway.

Is there a way to do this?

Answer 1

The only way that comes to mind is looking at the mySQL process itself and watching its memory footprint. I'm sure there are tools for this, however it may become difficult to find out the right process on a crowded machine.

Answer 2

I just found that MySQL has a profiler built into it as of 5.0.37:

http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

But there's a slight snag: MEMORY - is not currently implemented