> 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.