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 2227 Eric Coll 04/07/2012 01:20AM
Re: Identical servers different plan for the same query 977 Eric Coll 04/07/2012 09:55AM
Re: Identical servers different plan for the same query 973 irek kordirko 04/07/2012 02:54PM
Re: Identical servers different plan for the same query 1222 Rick James 04/08/2012 12:20PM
Re: Identical servers different plan for the same query 1224 Eric Coll 04/09/2012 10:24AM
Re: Identical servers different plan for the same query 909 Eric Coll 04/09/2012 10:35AM
Re: Identical servers different plan for the same query 896 Eric Coll 04/09/2012 10:37AM
Re: Identical servers different plan for the same query 964 Eric Coll 04/09/2012 10:42AM
Re: Identical servers different plan for the same query 891 Eric Coll 04/10/2012 03:28PM
Re: Identical servers different plan for the same query 987 Rick James 04/10/2012 10:29PM
Re: Identical servers different plan for the same query 921 Eric Coll 04/11/2012 10:05AM
Re: Identical servers different plan for the same query 974 Rick James 04/12/2012 08:56AM
Re: Identical servers different plan for the same query 960 Eric Coll 04/12/2012 03:23PM
Re: Identical servers different plan for the same query 2120 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.