Re: Partitioning by date, and maintaining uniqueness

[IGG t]

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

I was never too concerned about this side of it, I didn't expect the gains to be that great here.


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

This was the primary reason for looking at partitions


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

sorry, I phrased this badly, it was the Created time that is filled in by the web (rather than using CURRENT_TIMESTAMP in the database). The id is indeed auto_increment from the database.


The problem I had was that by setting the Primary Key as (id, Created), I could potentially run the following queries:

INSERT INTO TableName (id, created) VALUES (1000,'2013-08-14 17:30:00');
INSERT INTO TableName (id, created) VALUES (1000,'2013-08-14 17:31:00');

and then I would have two id's of 1000. (this is purely theoretical as there is no reason for anyone to manually insert an id, but mistakes do happen!!)