MySQL Forums
Forum List  »  Partitioning

Re: Minimum number of rows for using partitioning
Posted by: Rick James
Date: December 14, 2009 12:17AM

(TIMESTAMP > xxx) AND (TIMESTAMP < xxx) -- what are the typical values of xxx? Perhaps units of days? Or hours? Do you really need resolution of 5 seconds? What do you do with the SELECT? Look for min/max/average/total? I'm leading toward summarizing the information over some bigger unit, say 5 minutes. This would require 1/60th the number of rows. The data would have other things -- like min, max, count, sum. Average = sum/count.

Once you have summarized an hour's data, you could toss it. Then your dataset would be so small that the discussion of Partition would be moot.

OK, back to your question. I don't think either form of partitioning will help performance for either your inserts or your selects.

You also said there were performance problems "due to indexing". Are you referring to secondary keys on the table? Please show us the SHOW CREATE TABLE. What you describe so far would do well with a compound index on (machine, timestamp). This index would not be a performance problem -- it would have N hotspots, assuming N machines, and assuming data comes in roughly in chronological order. Are there other indexes? If so, what? (And will summarization avoid the need for them?)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Minimum number of rows for using partitioning
2308
December 14, 2009 12:17AM


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.