3 ms·
auto increment considered harmful, from joshu: http://joshua.schachter.org/2007/01/autoincrement.html http://joshua.schachter.org/2007/01/autoincrement.html Y
by bl4k 16y ago
auto increment considered harmful, from joshu:
http://joshua.schachter.org/2007/01/autoincrement.html http://joshua.schachter.org/2007/01/autoincrement.html
Your tables almost always have a better unique key to use as a primary key other than auto increment (which sorts your table by the order they were inserted in - almost always useless outside of a blog).
Getting out of the habit of beginning the design of your tables with ID int autoincrement is a good thing. Even better if web frameworks stopped depending on it and setting it as the default primary key and ID used in views/routes.
- GFischer 16y agoAutoincrement is good sometimes: we had some problems with slow inserts in a production SQL server. It turned out that the problem was that the primary key was set up as a clustered index, and we were doing inserts in a non sequential way, so the server had to rearrange the B-tree every time a row was inserted. So yes, sorting your index sequentially can have important performance benefits (at least for Microsoft SQL Server and clustered indexes, and a table with lots of inserts). Edit: read the above link, it only says not to use an URL with identifiers (I can agree with that), and that MySQL with InnoDB has some problems with clustered indexes. The title is very misleading.
- jasonlotito 16y agoThe article linked here makes a bad case against auto incrementing. In fact, I'm the opposite. Every field should have an ID that has nothing to do with the data being stored. That, the record should not contain the (in the case of MySQL) primary key. Take the traditional users table. username password email You might suggest that username can be the key. And sure, it will be a unique key. But, internally, I don't want to operate on the username. I'd rather operate on an internal value that is not related to the rest of the record. So I add an ID. This ID doesn't have to be exposed to the user. However, should it ever pass that I need to let users change their username, I can easily by editing a single entries field. Basically, your application logic shouldn't impact your business logic. Referencing record 12345 should also reference the same record, regardless of what else is changed.
- thibaut_barrere 16y agoI believe the OP was suggesting using a non-business key that isn't an incremented integer - typically some kind of guid. This allows to shard the data across multiples machines more efficiently, too. MongoDB (amongst others) use this by default.
- jasonlotito 16y agoMaybe. If he was, it wasn't clear, especially with "Your tables almost always have a better unique key." I took that as meaning using some element of the record. Regardless, you make a good point. =)
- balding_n_tired 16y agoYou might want to Google for C.J. Date's essay on the problems with "intelligent keys".