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: Eric Coll ()
Date: April 11, 2012 10:05AM

I didn't explain myself correctly. MySQL always clusters the data (physically sorted on disk). If I don't provide a primary/unique key, then it will create its own sequential order. Is that right?

We want to cluster the data by timestamp, so we are forced to create a unique or primary index by timestamp. This is a big limitation in my sql, not being able to create clustered non unique indexes. We do not have a good unique key by timestamp, to make it smaller we will need to create a surrogote key.

This is more of a data warehouse app. We don't need a PK as we never do any single record operation. The reason why you 'should' always have a PK in RDBMS is so each record can be uniquely identified which we don't need.

That said, I agree that the PK is too long, and probably the best fix we can do here.

We have over half a million accounts, and growing. Not sure why it seems that we have 91.

The InnoDB buffer is 9GB.

The data is partitioned by weeks.
PARTITION BY RANGE ( timestamp)
(PARTITION p_20120826 VALUES LESS THAN (1345939200)
...)

Everything seems to be working well, except a double group by query that takes about 3 minutes, and the strange thing is that the explain takes as long and it gets stuck copying a temp table as if the explain actually executes the query.

Options: ReplyQuote


Subject Views Written By Posted
Identical servers different plan for the same query 1416 Eric Coll 04/07/2012 01:20AM
Re: Identical servers different plan for the same query 613 Eric Coll 04/07/2012 09:55AM
Re: Identical servers different plan for the same query 610 irek kordirko 04/07/2012 02:54PM
Re: Identical servers different plan for the same query 793 Rick James 04/08/2012 12:20PM
Re: Identical servers different plan for the same query 809 Eric Coll 04/09/2012 10:24AM
Re: Identical servers different plan for the same query 580 Eric Coll 04/09/2012 10:35AM
Re: Identical servers different plan for the same query 584 Eric Coll 04/09/2012 10:37AM
Re: Identical servers different plan for the same query 554 Eric Coll 04/09/2012 10:42AM
Re: Identical servers different plan for the same query 551 Eric Coll 04/10/2012 03:28PM
Re: Identical servers different plan for the same query 560 Rick James 04/10/2012 10:29PM
Re: Identical servers different plan for the same query 569 Eric Coll 04/11/2012 10:05AM
Re: Identical servers different plan for the same query 604 Rick James 04/12/2012 08:56AM
Re: Identical servers different plan for the same query 562 Eric Coll 04/12/2012 03:23PM
Re: Identical servers different plan for the same query 579 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.