autoincrement Considered
via Mark Pilgrim, Joshua Schacter considers autoincrement harmful. The summation of the problem is:
The first problem is that you will be tempted to use the internal identifiers in external URLs.
Joshua outlines a few problems with that that are interesting in some contexts, but the problem that I've personally run into is that sometimes, the records in your database have URLs as a primary, or part of a primary, key. So the temptation is now to expose those records with an identifier that now replaces a perfectly good primary identifier. There was an old Daily WTF where the database "ran out of dates", which was pretty funny, but mainly as an extreme case of what Joshua's identified. It's a funny post, but also an important point about how we casually substitute identities in a relational DB. Artificial primary keys are efficient but can introduce a pernicious aliasing effect. I've since created a personal rule: never expose an internal identifier in a web interface. Doing so is the web equivalent of passing pointers around - "here's a pointer to an internal data structure! Have fun hacking!".
Joshua's point about clustered indexes in MySQL does carry over to other systems as well. MySQL places a severe requirement around autoincrement columns (that they be the primary key), but the temptation certainly exists in any DB system to create these as a clustered index, which will affect your external interfaces. You want your external interface to work efficiently with your database, so there's significant pressure to design your interface to use the clustered index as often as possible. I also recall that autoincrement columns can cause problems with replication on some DB systems - IIRC, older versions of SQL Server had this problem, though my memory is possibly faulty.
One of the comments is Joshua's blog asks for "the right way". This is a really good question; some fields that you might want to index on (in particular, variable-length fields or BLOBs) are inherently inefficient. One trick for these cases is to create a hash of the data and use that as an index. You'll still have to worry about collisions, but you'll avoid the other problems with autoincrement.