Comments on: Reasons to use AUTO_INCREMENT columns on InnoDB

By: shlomi

shlomi — Wed, 23 Mar 2011 06:52:19 +0000

@Shantanu,
You are not foolish, and neither is Sheeri nor Mark. There are reasons for and against using AUTO_INCREMENT (see comments by Lachlan & Justin).
Best is to think about the reasons, not just take someone’s advice as granted. In our case, please read two paragraphs down the same page (221) and see that the authors provide with a case FOR using a surrogate key over a natural key. Does that contradict what was written before? No. Because there are cases FOR and cases AGAINST.
There is reason to their advice of preferring a natural key; first answer to yourself the question: “what is their reason?”

Regards

By: Shantanu Oak

Shantanu Oak — Wed, 23 Mar 2011 06:04:10 +0000

Call me bookish. But from MySQL Administrator’s Bible By Sheeri Cabral, Page 221
First, a natural key should be looked for, and if a natural key cannot be determined, then a surrogate key may be appropriate.

By: shlomi

shlomi — Wed, 23 Mar 2011 05:51:33 +0000

@flytwokites:
joins have nothing in particular to do with PRIMARY KEYs.
You don;t even have to have *any* index, although indexes are the only MySQL optimization for joins (the alternative is full-join, usually unacceptable).

By: shlomi

shlomi — Wed, 23 Mar 2011 05:50:12 +0000

@Lachlan,
Indeed so.

@Justin,
Indeed so. I guess for any bullet I put there could be a counter-bullet.
The sentence “In many datasets, more recent entries are often being accessed more” can also read “and in many others – they aren’t”.
If the natural key is the one being mainly accessed, AND it is small enough (an integer, a couple integers etc.), then I agree.
If, however, it is a very large key, then I must disagree (see my reference to an older post.

By: flytwokites

flytwokites — Wed, 23 Mar 2011 05:25:07 +0000

An auto increment PK is needed for m2m join tables?

By: Justin Swanhart

Justin Swanhart — Tue, 22 Mar 2011 22:44:05 +0000

I mean to say “mainly access the table by the ‘natural key'” then it might make sense to use it.

By: Justin Swanhart

Justin Swanhart — Tue, 22 Mar 2011 22:42:55 +0000

This is all true, but if you have to create a secondary key on the natural key order to access the table AND you mainly access the table by the primary key, then it might make sense to organize the table around the natural key.

You will end up significantly increasing random IO once a table becomes large if you use a surrogate key but don’t access the data via it. Of course, the adaptive hash index could help with this, but it isn’t guaranteed if your access pattern can’t utilize it (perhaps range scans)..

By: Lachlan Mulcahy

Lachlan Mulcahy — Tue, 22 Mar 2011 21:02:33 +0000

Also keep in mind that one of the things you note could be a reason FOR using Natural PRIMARY KEYs. That is to say, if the fields a query requests are only in the PRIMARY KEY and one SECONDARY KEY, then it is possible to serve the query entirely from the SECONDARY KEY.

By: Daniël van Eeden

Daniël van Eeden — Tue, 22 Mar 2011 12:40:08 +0000

Great Post!

In 5.0 there were some issues with auto_inc performance, so this might only be a good practice if you’re using 5.1 or 5.5

Autinc is also tunable:
http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html