MySQL Forums
Forum List  »  Optimizer & Parser

Re: Identical servers different plan for the same query
Posted by: Rick James
Date: April 10, 2012 10:29PM

> The partitioned table requires a unique index and that was the only unique key.
What? A partitioned table requires that every UNIQUE (or PRIMARY) key contain the "partition key". InnoDB tables _should_ have an explicit PRIMARY KEY.

What is the PARTITION key? What is the PARTITION type?

155GB of data? We should look at the datatypes to see if they can be made smaller, for example:
You have 91 accounts? Yet `account` takes 4 bytes?

Note that, in InnoDB, every secondary key (of which you have 2) contains a copy of the PRIMARY KEY. That explains why the Index_length is bigger than the Data_length.

Is innodb_buffer_pool_size about 70% of available RAM?

SELECT * FROM information_schema.PARTITONS WHERE TABLE_NAME = 'pool_usage';

Options: ReplyQuote

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.