MySQL Forums
Forum List  »  Optimizer & Parser

Re: Identical servers different plan for the same query
Posted by: Rick James
Date: April 13, 2012 03:49PM

> 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.

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.