Does using an auto increment of 1 on numeric PK hurts performance

auto-incrementperformanceprimary-key

A teacher of mine said that having an numeric PK with auto increment is a bad practice because it hurts performance.

He also said that Oracle database doesn't have an easy way to set up auto increment because of this.

He said that increment should be done on app instead of DB.

The performance penalty, if I remember correctly, would happen while inserting many rows, it had something to do with allocating a certain number of PKs IDs or something.

Is this true? If yes why?

Best Answer

I think the answer is that "it depends". In a lot of use cases, using an auto increment PK is good practice.

In InnoDB, the default storage engine in MySQL and MariaDB, the performance penalty of using the auto increment feature depends on the auto increment lock mode used. Two of the modes come at the cost of a table-level lock that is released once the statement has completed, which means only one statement can run at any one time. The third option (interleaved lock mode) doesn't lock the tables.

Oracle (12c) actually has auto incrementing keys (identity columns). In previous versions you had to use sequences, which can serve the same purpose, and more. They are a bit more cumbersome to use, though, than auto incrementing keys.

The suggestion that applications should be generating these ID numbers on their own without the aid of the RDBMS' sequence or auto increment features really isn't realistic in a lot of use cases.

Related Solutions

Mysql – Auto Increment Primary ID inserting random values

I'm having a very difficult time thinking of any possible misbehaving mechanism inside MySQL or InnoDB that could cause this behavior as the result of a bug, without throwing errors or causing a crash. It seems much more likely that the table is being updated by the application. Instead of writing new rows, something is finding and updating old ones.

If that's not supposed to be happening, I would put a trigger on this table to block those updates.

mysql> CREATE TRIGGER txn_bu BEFORE UPDATE ON txn FOR EACH ROW 
       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'this table does not support updates';
Query OK, 0 rows affected (0.14 sec)

mysql> UPDATE txn SET `timestamp` = NOW() WHERE id = 1;
ERROR 1644 (45000): this table does not support updates

mysql>

This makes directly updating rows impossible. Note that you don't need the usual DELIMITER change and BEGIN/END blocks because the trigger is comprised of a single simple statement.

If the table is never updated then the trigger would never fire, so there's no significant overhead associated with having it defined. If it sees an update query then it's going to throw an exception back to the application which should generate exception reports or support tickets. Arguably, if your application has a defect that's stomping data, you want it to fail hard, instead of soft.

Mysql – make MariaDB Auto-Increment-Increment dynamic

No, the auto increment functionality of MySQL (or MariaDB) does not provide some kind of "dynamic" auto increment values like you request. The formula you arranged is all MySQL can do.

In your case you might be better of in just abandoning the auto increment column and use a custom column instead. You can fill this for example with UUIDs. This does not guarantee uniqueness of generated IDs, but the possibility to generate the exact same IDs is very low.

Best Answer

Related Solutions

Mysql – Auto Increment Primary ID inserting random values

Mysql – make MariaDB Auto-Increment-Increment dynamic

Related Question