MySQL Forums
Forum List  »  Partitioning

Re: Partitioning by date, and maintaining uniqueness
Posted by: Rick James
Date: August 14, 2013 09:20AM

> we can partition by month (hopefully making selects quicker),

Usually not. Let's see the SELECTs so we can discuss this.

> but also because we can simply drop the relevant partition at the start of each month, quickly and cleanly.

True. http://mysql.rjweb.org/doc.php/partitionmaint

> But to partition the data by 'created' I would need change the Primary Key to include the 'created' column.

True. PRIMARY KEY (id, created)

> which is filled in by the web app when a new entry is created

Well, if the app is creating the numbers, then do not use AUTO_INCREMENT. If the database is creating the numbers, then all is well.

> So as far as I can tell, the choice is basically:
> 1) Don't have partitions and maintain Uniqueness of the ID's
> 2) Have partitions, but risk losing the uniqueness of the ID's

or
3) Use AUTO_INCREMENT to get uniqueness with partitions, but shift the number generation from app to database.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partitioning by date, and maintaining uniqueness
1257
August 14, 2013 09:20AM


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.