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 1546 Eric Coll 04/07/2012 01:20AM
Re: Identical servers different plan for the same query 655 Eric Coll 04/07/2012 09:55AM
Re: Identical servers different plan for the same query 649 irek kordirko 04/07/2012 02:54PM
Re: Identical servers different plan for the same query 856 Rick James 04/08/2012 12:20PM
Re: Identical servers different plan for the same query 870 Eric Coll 04/09/2012 10:24AM
Re: Identical servers different plan for the same query 612 Eric Coll 04/09/2012 10:35AM
Re: Identical servers different plan for the same query 617 Eric Coll 04/09/2012 10:37AM
Re: Identical servers different plan for the same query 598 Eric Coll 04/09/2012 10:42AM
Re: Identical servers different plan for the same query 592 Eric Coll 04/10/2012 03:28PM
Re: Identical servers different plan for the same query 623 Rick James 04/10/2012 10:29PM
Re: Identical servers different plan for the same query 620 Eric Coll 04/11/2012 10:05AM
Re: Identical servers different plan for the same query 657 Rick James 04/12/2012 08:56AM
Re: Identical servers different plan for the same query 622 Eric Coll 04/12/2012 03:23PM
Re: Identical servers different plan for the same query 647 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.