upsert Auto Increment is increasing a lot

[ChrisSantiago82]

I have started using upsert (with MySql) and it's very cool and useful for me. I'm using it to import data from another system. But I have noticed that the Auto Increment of the id field jumps up a lot.

For example here, the initial data load, had 41'284 entries. Until here everything is correct. It's a job that runs in every hour because the other system can bring new data. Now the Auto Increment is at 604'645, but there are just 14 more entries since the first load.

Does anybody has the same experience with upsert?

To me it almost looks like that it does the auto increment without checking if it was a new entry or if a entry was updated.

[staudenmeir]

Unfortunately, that's how UPSERT queries work on MySQL: https://stackoverflow.com/a/23517191/4848587

[ChrisSantiago82]

Yes, that is what I thought. And it isn't a real issue, just doesn't look that nice.

[imrealashu]

I know it's an old issue but is there any work around this? the problem is we deal with many records and due to this nature it would soon hit the max limit of the bigInteger field.

Thanks in advance!

[henzeb]

Not using auto increment but uuid's?

[imrealashu]

It's a good idea but can't expect to change the entire application structure because of this issue.

[henzeb]

You could of course keep the numeric id's while storing new records with uuid's.

Another solution could be reordening the ID's, but depending on the size of the table and the references to those id's, this might take a while.

This simulates your table, a lot of keys missing.

create table temp (
  id int(12) auto_increment primary key,
  txt varchar(2) not null
);

insert into temp (`txt`)
VALUES ('aa'), ('bb'),('cc'), ('dd'), ('ee'), ('ff');

delete from temp where id in (3, 5);

You'd have

id	txt
1	aa
2	bb
4	dd
6	ff

Running:

UPDATE
    temp AS m
  JOIN
    ( SELECT Id, row_number() OVER (ORDER BY Id) AS rn
      FROM temp
    ) AS sub
  ON  m.Id = sub.Id
SET
    m.id = sub.rn;

You'd have

id	txt
1	aa
2	bb
3	dd
4	ff

finally you'd run alter table temp AUTO_INCREMENT = 1; to reset the auto_increment (next increment would be 5)