Re: Identical servers different plan for the same query

[Eric Coll]

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.