Re: Identical servers different plan for the same query
Posted by:
Rick James
Date: April 12, 2012 08:56AM
> I didn't explain myself correctly. MySQL always clusters the data (physically sorted on disk).
InnoDB clusters on the PRIMARY KEY.
> The data is partitioned by weeks.
That gives you a crude form of clustering on time.
> If I don't provide a primary/unique key, then it will create its own sequential order. Is that right?
Yes (for InnoDB). It will be a hidden 6-byte number. It is usually better to create your own 4-byte INT UNSIGNED AUTO_INCREMENT.
> We want to cluster the data by timestamp, so we are forced to create a unique or primary index by timestamp.
No necessarily. If the data comes in in chronological order, or even roughly so, then the AUTO_INC mentioned above would have the effect of clustering.
> This is a big limitation in my sql
Limitation, yes. Big, I would argue not.
> 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.
EXPLAIN evaluates uncorrelated subqueries fully, so it can do the outer explain. (An unfortunate limitation of EXPLAIN.)
> a double group by query
May we see the query and its EXPLAIN.