Re: Larger than expected tmp files querying InnoDB format database

[Michael Lueck]

Rick James Wrote:
-------------------------------------------------------
> That leads to my suggestion of running
> the query twice -- first time may (or may not) do
> a lot of I/O; the second time is more
> representative and repeatable.

Even with the original OSS/FS project queries as-was, subsequent runs return the result set instantly. My complaint was/is the excessive tmp swap file MySQL creates. Before I could understand that the server was "low" on RAM. But now, MySQL is leaving 2GB of RAM snoozing while it writes a 2GB tmp file to perform the initial query after I bounced MySQL. I would have thought MySQL should exhaust memory available in its pool of resources before swapping to disk, no?

I seek some illusive configuration setting which will execute queries utilizing fast RAM, completing them faster, so that the RAM is available sooner for next queries. I was told "Server needs more memory," Fine, I added memory, NOW USE IT ALREADY! (banging head)

> Hmmm....
>
> First, let's focus on the new subquery:
>
> The ORDER BY is spread across two tables. Hence
> it must gather all possible rows before doing the
> ORDER BY and LIMIT.

The contribution table has just over 94,000 records in it. The contacts table has just under 18,000 records in it.


> I am fishing for whether the GROUP BY is
> necessary, and whether the LEFT is necessary. I
> suspect neither is necessary.
>
> If you could remove contact_a.id from the ORDER
> BY, there might be more optimization avenues open.


I was trying to maintain as much of the look and feel of the OSS/FS project's query as possible... seeking some silver bullet of optimization possibly via subquery which would cause MySQL to significantly drop the size of the tmp file it seems to despretly desire to utilize.

I am thankful,

Michael Lueck
Lueck Data Systems
http://www.lueckdatasystems.com/