MySQL Forums
Forum List  »  InnoDB

Re: Larger than expected tmp files querying InnoDB format database
Posted by: Rick James
Date: August 24, 2014 08:53PM

> I bounce MySQL to flush the query cache

Instead, do:
SELECT SQL_NO_CACHE ...;

That deals with the Query Cache, but does not deal with other caches. The other caches (esp the innodb_buffer_pool) should not be flushed. After all, in a 'production' system, it 'never' gets flushed. 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.

> and not a bit faster nor uses less tmp disk space

Hmmm....

First, let's focus on the new subquery:
SELECT civicrm_contribution.id
           FROM civicrm_contact contact_a
           LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
           WHERE (civicrm_contribution.is_test = 0)
             AND (contact_a.is_deleted = 0)
           GROUP BY civicrm_contribution.id
           ORDER BY civicrm_contribution.receive_date DESC,
                    contact_a.id
           LIMIT 0, 10;
The ORDER BY is spread across two tables. Hence it must gather all possible rows before doing the ORDER BY and LIMIT. To see the impact of this, what COUNT do you get from
SELECT COUNT(*)
           FROM civicrm_contact contact_a
           LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
           WHERE (civicrm_contribution.is_test = 0)
             AND (contact_a.is_deleted = 0);
It has to gather that many rows into a tmp table, in spite of the LIMIT 10.

While you are at it, please run these counts:
SELECT COUNT(*), COUNT(DISTINCT civicrm_contribution.id)
           FROM civicrm_contact contact_a
           LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
           WHERE (civicrm_contribution.is_test = 0)
             AND (contact_a.is_deleted = 0);
SELECT COUNT(*), COUNT(DISTINCT civicrm_contribution.id)
           FROM civicrm_contact contact_a
            JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
           WHERE (civicrm_contribution.is_test = 0)
             AND (contact_a.is_deleted = 0);
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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Larger than expected tmp files querying InnoDB format database
1018
August 24, 2014 08:53PM


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.