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?

  • 1
    Holes in the primary key are an aesthetic issue,if you really need consecutive numbers you have a bad design. – Mihai Aug 12 '14 at 9:35
  • The surrogate key is purely for update performance, I don't use the key anywhere else but I can't help feeling that this would still slow index performance. Is there any resource anywhere to say otherwise? – ydaetskcoR Aug 12 '14 at 9:36
  • 2
    Index Seeks work just as well with holes.I doubt anyone here will tell you differently but wait for a mysql expert to give you a more complete answer. – Mihai Aug 12 '14 at 9:40

Create a temporary table and insert all values into it. Insert into your main_table a select statement where you join your temp_table with your main_table where main_table.uniqueKey IS NULL. The select statement will return only the values that are not present in the main table.

INSERT INTO main_table (page, pageId) (SELECT temp_table.* FROM temp_table 
                      LEFT JOIN main_table as t1 ON temp_table.uniqueCoumn = t1.uniqueCoumn
                      WHERE t1.uniqueCoumn IS NULL)

The downside is this will not update old keys, so you will need to update those. Unless you're worried about auto_increment column overflowing, don't do this, gaps are completely fine.

Your Answer

