MySQL Forums
Forum List  »  InnoDB

Re: Larger than expected tmp files querying InnoDB format database
Posted by: Rick James
Date: July 12, 2014 09:20AM

> "each query generates a 2GB tmp file" which I do not believe is an acceptable norm.

Unacceptable, I agree. However, the query in question grow linearly as civicrm_contribution grows. (The LIMIT, as I discussed, comes into play too late to prevent such growth.) The sizes of a few of the other tables is also critical. civicrm_financial_account may be such. A guess: the size of the tmp table is proportional to the product of the sizes of civicrm_financial_account and civicrm_contribution.

Do you need all those fields in the output? Can you remove some of them? More importantly, remove any JOINs that would be no longer necessary. This may have a minor or a major impact on the tmp table size. (And on the speed of the query.)

Back to an earlier comment... If you could shrink some of the VARCHAR sizes, it would help shrink the tmp table size. 128 is excessive for middle_name. However, computing MAX(LENGTH(middle_name)) and using that would be unsafe.

CSV for dumping -- That seems reasonable (at some level). For exmple, I would expect a middle_name of 'Sam' to occupy only 6 characters (Sam, plus quotes and one comma). So, I would not expect a megabyte per row. Take a sample dump and look at it; we are missing something.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Larger than expected tmp files querying InnoDB format database
1258
July 12, 2014 09:20AM


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.