Re: Identical servers different plan for the same query

[Rick James]

> the autoincrement id as a primary key works with a partitioned table?
Yes. The only requirement (I think) is that some index (not necessarily UNIQUE) _start_ with that column.

> Would that be a good choice for a PK?
That question is answerable after seeing all the important SELECTs.
Looking just at
> where timestamp between unix_timestamp('2012-03-10') and unix_timestamp('2012-04-10') and
> account in ('833008473584')
I would say:
If it is not partitioned, or partitioned on timestamp, then this index would be optimal:
(account, timestamp, ...)
Whether that is the PK, UNIQUE, or plain INDEX is a separable question.

Potential bug or inefficiency:
> where timestamp between unix_timestamp('2012-03-10') and unix_timestamp('2012-04-10') and
That includes an extra midnight. This could lead to scanning an extra partition. Suggest this coding pattern:
where timestamp >= unix_timestamp('2012-03-10')
and timestamp < unix_timestamp('2012-03-10' + interval 1 month)
(Note "<", not "<=")

I don't see the definition of idx_reservation_account_time, so I cannot comment on the EXPLAIN.
Nor do I see the datatypes, so I cannot guess what the "70" is composed of.
SHOW CREATE TABLE