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: http://mysql.rjweb.org/doc.php/partitionmaint

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


Subject
Views
Written By
Posted
2265
November 29, 2013 05:03AM
1290
November 29, 2013 06:22AM
1401
November 30, 2013 02:18PM
1203
December 04, 2013 12:38AM
1253
December 05, 2013 03:48AM
Re: Am I doing this correctly?
1304
December 05, 2013 10:21AM
1234
December 10, 2013 09:49AM
1276
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.