MySQL Forums
Forum List  »  Partitioning

Re: Am I doing this correctly?
Posted by: Rick James
Date: December 05, 2013 10:21AM

INSERTing 115K rows spread out across the day per day? That is about 1/second. 100/second is the first point at which performance _may_ be an issue.

On the other hand, DELETEing 115K (or 3M rows for a month) at one time would have impact on the table, especially if it cannot use an index. Hence, building PARTITIONs is very beneficial if it lets you DROP PARTITION instead of DELETE.

So, yes, PARTITIONing is a good idea. Further discussion:

Please note that this will not be optimized at all:
WHERE YEAR (DateStamp) = 2011 AND MONTH (DateStamp) = 6

Assuming DateStamp is DATETIME or TIMESTAMP or DATE and is INDEXed, do this instead:
WHERE DateStamp >= '2011-06-01'
AND DateStamp < '2011-06-01' + INTERVAL 1 MONTH

Options: ReplyQuote

Written By
November 29, 2013 05:03AM
November 29, 2013 06:22AM
November 30, 2013 02:18PM
December 04, 2013 12:38AM
December 05, 2013 03:48AM
Re: Am I doing this correctly?
December 05, 2013 10:21AM
December 10, 2013 09:49AM
December 05, 2013 03:51AM

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.