MySQL Forums
Forum List  »  InnoDB

Re: Larger than expected tmp files querying InnoDB format database
Posted by: Rick James
Date: July 08, 2014 02:45PM

Check, optimize, and analyze are rarely of any use for InnoDB.

Please provide SHOW CREATE TABLE for each of the tables.
And let's see the SELECT.

A guess -- you have a lot of VARCHAR(255) fields that turned into CHAR(255) in the tmp table.

I see "Using index" in a few cases; perhaps more could be implemented.

It is not until the 5th entry in the EXPLAIN that a large number of "rows" shows up. This sometimes indicates a lack of index. (Need SHOW CREATE and SELECT to be more specific.)

I got lost in our/peer/current/production/5.0/5.5/new/etc. Are you saying that 5.5 is generating a bigger tmp table and is taking longer?

Please run a diff between SHOW VARIABLES on the two systems. (5.5 will have a lot more VARIABLEs; are the common ones the same?) And see if the EXPLAINs are the 'same'. "rows" is likely to be different, but the rest should be identical. If it is not identical, then that may be a clue.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Larger than expected tmp files querying InnoDB format database
1259
July 08, 2014 02:45PM


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.