Alternatives to MySQL Partitioning, so we can have a Unique Index I have a DBA vs Developer Issue here, and am wondering if there are any alternatives to partitioning that I can employ here.

I have a table as follows:

CREATE TABLE `tracking` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CustId` int(10) unsigned DEFAULT NULL,
  `SourceName` varchar(32) DEFAULT NULL,
  `SourceId` int(10) unsigned DEFAULT NULL,
  `Created` datetime NOT NULL,
  PRIMARY KEY (`Id`,`Created`),
  KEY `Cust_Source`(`CustId`,`SourceName`,`SourceId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( TO_DAYS(Created))
(PARTITION `201903` VALUES LESS THAN (737515) ENGINE = InnoDB,
 PARTITION `201904` VALUES LESS THAN (737545) ENGINE = InnoDB,
 PARTITION `201905` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;


As you can see, I like to partition my tables, as we only ever keep 2 months data in the live system, and this way I can automatically DROP the oldest partition each month. 

However, my developer has asked if I can change the Cust_Source Index to be a UNIQUE Index, to prevent duplicate data being added by mistake. But so doing means I need to lose the partitioning. As:

"Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function"


And of course, if I include the created field in the Index, we could get Duplicate data, created at different times.

Any suggestions?

This needs to work on MySQL 5.5 and 5.7 (although we'll be retiring 5.5 soon, hopefully).
Since you won't have even a million rows in the table, don't bother to PARTITION it.  Then you won't have any trouble with the UNIQUEness constraint.
Change the indexing:


Plan A:

PRIMARY KEY(Id),   -- notice: `Created` was removed
INDEX(Created),    -- for purging
UNIQUE(`CustId`,`SourceName`,`SourceId`)  -- as desired


Plan B (this assumes you have no real use for id):

PRIMARY KEY(`CustId`,`SourceName`,`SourceId`)  -- Unique
INDEX(Created)    -- for purging



Purge daily, not monthly.  This is because DELETE is much slower than DROP PARTITION.  If you still run into performance troubles:  Using Plan B, write a loop to delete the rows for each CustId one at a time.


(I prefer Plan B for your situation.)

(My comments apply to all versions.)

Usually, each partition has 4M-7M of "free" space.  Hence a partitioned table is inherently bigger than the non-partitioned equivalent.