MySQL Forums
Forum List  »  Optimizer & Parser

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

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.