0

I have a big table with ID column set as bigint auto_increment. At some time in history we decided to generate IDs on client side as some pseudo-random number.

My question is simple: given that the table column is still set as auto_increment, does this affect performance negatively even though we ALWAYS supply ID in INSERT queries? Is this significant/should I bother to change the schema? The table is EXTREMELY huge and busy, I am afraid I cannot do this without downtime.

Improve this question
8
  • Study AUTO_INCREMENT Handling in InnoDB. Do you understand that the only possible answer is "too low facts"?
    – Akina
    Commented Dec 18, 2019 at 13:38
  • 2
    Just to clarify, you've started inserting explicit values into an auto-increment column, and you are asking whether there's a significant performance difference between inserting explicit values into an auto-increment column and inserting them into a regular (non-auto-increment) column. Would that be an accurate summary?
    – Andriy M
    Commented Dec 18, 2019 at 15:25
  • @TomV-TeamMonica we had problems with INSERTing, it was taking too long (this table is extremely busy). So we came with quite a good way to make unique numbers. The algorithm is good, in the very rare occasion conflict occurs we handle it in code.
    – michnovka
    Commented Dec 18, 2019 at 15:52
  • @AndriyM Exactly. We started inserting explicit values into an auto-increment column, and need to know whether there's a significant performance difference between inserting explicit values into an auto-increment column and inserting them into a regular (non-auto-increment) column
    – michnovka
    Commented Dec 18, 2019 at 15:53
  • @TomV-TeamMonica yeah, I know, but the table likely cant be modified on the fly without downtime. Thats why I am asking
    – michnovka
    Commented Dec 18, 2019 at 16:16

1 Answer 1

Reset to default
1

After statically looking at the MySQL (5.7.28) code it makes no difference. Auto_increment locks within innodb only happen if fetching a new auto_increment value (via here), or altering the table to have a different auto_increment value.

Its current dormant status is not causing any impact to you.

Improve this answer
3
  • However, it does need to see if the value is already in the table, correct? And perhaps that uniqueness check is obviated by necessarily grabbing the nex auto_inc id?
    – Rick James
    Commented Dec 20, 2019 at 0:34
  • @RickJames: As someone working with SQL Server, I wouldn't expect uniqueness check to be part of auto-increment column implementation. An IDENTITY column in SQL Server certainly doesn't work that way, unless you also define the column as unique or PK. I guess auto-inc columns work differently in MySQL. On a different note, when you are inserting an explicit value into an auto-inc column and it's greater than the last auto-generated value, MySQL needs to remember it because it will generate the next value based on it, so I would think that should have some, however minimal, impact too.
    – Andriy M
    Commented Dec 20, 2019 at 6:54
  • @AndriyM - Auto_inc and IDENTITY and Sequence are rather different animals. MySQL's auto_inc mostly has a default that is one more than the max. Then there is a lot of careful code to optimize it. It must be the first column of some index, not necessarily the PK or a UNIQUE key. MySQL's PK is, by definition, a UNQUE index and is (for InnoDB), clustered with the data.
    – Rick James
    Commented Dec 20, 2019 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.