I have an application that is creating large holes in the auto incremented primary surrogate key to the table as it uses lots of
INSERT ... ON DUPLICATE KEY UPDATE queries. I'm looking into changing the application to look up the data first and then perform a conditional insert or update if the data exists but it's potentially a bigger problem than I have time to solve right now.
The table has no foreign key relations (currently) so I'm planning on changing the primary key field from INT to BIGINT (potentially unsigned as well) to solve any problems on size limits.
I'm aware that my index size will increase (maybe as much as double) with the change from INT to BIGINT but are there any issues that will be caused by the holes in the primary key? My gut tells me that it should make the index less performant but digging around in documentation and Google doesn't confirm or deny this.
Another alternative available would be to recreate the table without the surrogate key and instead make a natural composite primary key of three fields (a date stamp and 2 other fields that are already held as a unique key). Would this be a better approach?