0

I have a table that holds various users by user_id with many other indexed columns of ids from third-party tools they use.

For example, table users

user_id    |    user_name    |    zendesk_id    |    mailchimp_id   |    todoist_id

We have crons configured to hit these third parties for a list of users so that our db has any new users inserted. In the event of a user_name change on the third party, we also want to update that information on our side to keep in sync.

user_id is a primary key with auto-increment. all third party service columns (ex. zendesk_id) are unique and indexed.

Per mysql documentation, auto-increment will not work properly when other columns are indexed (see similar, but not duplicate question explaining this: ON DUPLICATE KEY + AUTO INCREMENT issue mysql )

Since mysql has this documented problem, my question is this:

How can I keep a reasonably clean auto-increment on user_id when I am pulling full user lists from these third parties to insert new users and update existing users?

Andrew
  • 67
  • 1
  • 6
  • What's a "clean" auto-increment? – Bill Karwin Mar 22 '19 at 12:49
  • 1
    Reading the question you linked to, and also other questions it links to, I would guess you mean that you want your auto-increment values to be continuous (i.e. no missing values). **Don't.** Auto-increments are required to be unique, not continuous. Are you also going to renumber millions of primary keys if you delete a row? It's not worth it. Id's are not row numbers. – Bill Karwin Mar 22 '19 at 12:53
  • @billkarwin the goal is not perfect sequencing, the issue is that `on duplicate key update` increments 1 for each record. If I run a cron once a day for 5 third-parties, each third-party containing 250 records... that means my AI goes up 1,250 per day. And that might only generate 2-5 new users in the db. Eventually that number gets too big, and for no reason at all. – Andrew Mar 22 '19 at 16:38
  • So your primary key is going to reach the maximum integer in 1.7 million days? – Bill Karwin Mar 22 '19 at 17:30

0 Answers0