CODEWITHSUNDEEP
mysqldatabase-design
michnovka
michnovka

Reputation: 3389

Does having auto_increment ID degrade performance compared to no auto_increment?

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.

Upvotes: 1

Views: 3197

Answers (2)

Rick James
Rick James

Reputation: 142296

Trust the database to give you the "best" in most situations. There is a lot of code (at least in InnoDB) to make AUTO_INCREMENT fast and efficient, even when multiple connections are inserting into the same table.

Notes on AUTO_INCREMENT and home-grown IDs:

  • AUTO_INCREMENT probably suffers from fewer locking delays and deadlocks.
  • I would not focus on the primary key for optimization; look elsewhere.
  • Supplying your own ID means you have to send it to the server; this increases (by a tiny amount) the bandwidth and latency.
  • SELECT LAST_INSERT_ID() is not costly, and is isolated to the connection. That is, a class of race conditions are avoided.
  • If you are supplying a number to the auto_inc column, you are defeating many of its optimizations.
  • Do you have a second column with your generated id? That is, do you have both a PRIMARY KEY and UNIQUE key? If so, that is overhead, especially for INSERTs, which must deal with all unique keys.
  • "some pseudo-random number" -- How do you deal with occasional duplicates?
  • What do you mean by "extremely busy"? If that is mostly bumping a counter of social media "Likes", then moving that column to a parallel table ('vertical' partitioning) is likely to provide a significant overall optimization.

Please provide SHOW CREATE TABLE and the main queries (read and write) against the table. This will help focus this Q&A to better help you.

To change the PRIMARY KEY requires downtime. (I think that is the case even with MySQL 8.0's new ALTER optimizations.)

Upvotes: 1

Ruan
Ruan

Reputation: 344

Technically there would still be some performance impact through the use of an auto-increment column even though you supply your own values. The engine must still update the auto-increment sequence variable in the background so that a value could be generated in the event that no value is supplied in an insert query.

If you are using InnoDB then there will also be a lock applied to the table on insert to prevent duplicate auto-increment values. You can change the lock settings and turn it off if you want to be pedantic about performance.

With all this said, this should not have any impactful effect on real-world performance as the auto-increment sequence is a variable that the engine has quick access to.

If you haven't had any issues up to this point, just leave it as is as the performance will not degrade with additional records.

The official information regarding this topic is very limited, unfortunately.

Upvotes: 2

Related Questions