Skip navigation links

MySQL Forums :: Optimizer & Parser :: Identical servers different plan for the same query


Advanced Search

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


Subject Views Written By Posted
Identical servers different plan for the same query 1528 Eric Coll 04/07/2012 01:20AM
Re: Identical servers different plan for the same query 649 Eric Coll 04/07/2012 09:55AM
Re: Identical servers different plan for the same query 645 irek kordirko 04/07/2012 02:54PM
Re: Identical servers different plan for the same query 849 Rick James 04/08/2012 12:20PM
Re: Identical servers different plan for the same query 861 Eric Coll 04/09/2012 10:24AM
Re: Identical servers different plan for the same query 607 Eric Coll 04/09/2012 10:35AM
Re: Identical servers different plan for the same query 613 Eric Coll 04/09/2012 10:37AM
Re: Identical servers different plan for the same query 591 Eric Coll 04/09/2012 10:42AM
Re: Identical servers different plan for the same query 586 Eric Coll 04/10/2012 03:28PM
Re: Identical servers different plan for the same query 615 Rick James 04/10/2012 10:29PM
Re: Identical servers different plan for the same query 615 Eric Coll 04/11/2012 10:05AM
Re: Identical servers different plan for the same query 651 Rick James 04/12/2012 08:56AM
Re: Identical servers different plan for the same query 615 Eric Coll 04/12/2012 03:23PM
Re: Identical servers different plan for the same query 638 Rick James 04/13/2012 03:49PM


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.