Re: Identical servers different plan for the same query

[Rick James]

> 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';