MySQL Forums
Forum List  »  InnoDB

Re: Larger than expected tmp files querying InnoDB format database
Posted by: Michael Lueck
Date: August 20, 2014 12:48PM

Rick James Wrote:
-------------------------------------------------------
> Re: 2GB tmp file: Here are some techniques that
> work in _some_ situations. (I cannot predict
> whether they would help in this situation.)
>
> Example 1:
> SELECT ... FROM JOIN JOIN ...
> -->
> SELECT
> ( SELECT ... FROM ... ), ...
> FROM JOIN ...


Ahh, so it is more efficent to generate in a subquery a list of record unique ID's full details of which are requsted for, then go off and do the outer query with many JOIN statements only upon those unique record ID's? If so, that makes sense to me.

This query involved the CRM's contribution tracking table with 100,000+ records. It was selecting only the most recent 10 (I believe that number) contributions. So I dare say if this query was reworked to query for a list of the unique row ID's of 10 records, THEN go off doing the various JOIN statements for only those rows, that alone would work much faster.

Thank you.

<><><>

You did not comment on my "A" part of my previous post... how to tune MySQL to make more use of available RAM on the server and not run off to the tmp file option so much. Someone else suggested running the sample query, then run "mysql tuning-primer.sh" script, so I will head down that path first. Performance wise I call the present situation "better... much better" (query completes now in half of the origional time) however 2GB of RAM idle in the server configuration is screaming for utilization.

I am thankful,

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Larger than expected tmp files querying InnoDB format database
1041
August 20, 2014 12:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.