MySQL Forums
Forum List  »  Optimizer & Parser

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.

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.