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 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.
SHOW CREATE TABLE

Options: ReplyQuote


Subject Views Written By Posted
Identical servers different plan for the same query 1545 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 648 irek kordirko 04/07/2012 02:54PM
Re: Identical servers different plan for the same query 855 Rick James 04/08/2012 12:20PM
Re: Identical servers different plan for the same query 866 Eric Coll 04/09/2012 10:24AM
Re: Identical servers different plan for the same query 611 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 596 Eric Coll 04/09/2012 10:42AM
Re: Identical servers different plan for the same query 590 Eric Coll 04/10/2012 03:28PM
Re: Identical servers different plan for the same query 622 Rick James 04/10/2012 10:29PM
Re: Identical servers different plan for the same query 619 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 621 Eric Coll 04/12/2012 03:23PM
Re: Identical servers different plan for the same query 643 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.