3 ms·

"In fact, even in RDBMS’s developers should be using a natural key as their primary key instead of an auto-incremented ID. This will lead to better performance

"In fact, even in RDBMS’s developers should be using a natural key as their primary key instead of an auto-incremented ID. This will lead to better performance when the natural key is the most commonly used identifier, but is often not considered out of a habit." Primary/unique keys are indexed. Creating an index of strings instead of integers is surely going to result in a huge index and slow performance?

Amezarak 12y ago
Well, like everything, it depends on your database, your data and how its accessed. I've had a lot of varchar indexes in my time. The article makes it seem like your primary key and main index have to be the same, but that's not necessarily the case. Most DBs have some support for physically ordered indexes. So take username, for example. Let's say username has to be unique, but it can change. (It doesn't even matter too much if it's not totally unique, just mostly unique.) Let's say you're going to be doing most of your lookups in the user table by username. If performance is an issue, keep your auto-incrementing guaranteed-unique primary key and add a clustered index on user name. The data is now physically stored by its natural key, but you still have a nice unique, unchanging primary key. Lookups are very fast, though depending on the implementation, inserts/updates might not be so much. Postgres supports this with the CLUSTER command, although it has to be run manually - it doesn't keep up as updates and inserts come in. SQL Server calls it a clustered index and enforces the physical ordering on inserts and updates. Oracle has indexed-ordered tables which can achieve a similar effect, though IIRC the index has to be the PK (nothing to stop you from having a unique constraint on a sequence-generated column, though.)
- batbomb 12y ago
  In oracle, the index on an index ordered table only needs be a unique. So, for example: 1234 "John Doe" null null null 5824 "John Doe" "Sale 1" "item 1" $5 4321 "John Doe" "Sale 1" "item 2" $50 4382 "John Doe" "Sale 2" "item 1" $4 CREATE UNIQUE INDEX (name, sale, item);

Amezarak 12y ago

Well, like everything, it depends on your database, your data and how its accessed. I've had a lot of varchar indexes in my time. The article makes it seem like your primary key and main index have to be the same, but that's not necessarily the case. Most DBs have some support for physically ordered indexes. So take username, for example. Let's say username has to be unique, but it can change. (It doesn't even matter too much if it's not totally unique, just mostly unique.) Let's say you're going to be doing most of your lookups in the user table by username. If performance is an issue, keep your auto-incrementing guaranteed-unique primary key and add a clustered index on user name. The data is now physically stored by its natural key, but you still have a nice unique, unchanging primary key. Lookups are very fast, though depending on the implementation, inserts/updates might not be so much. Postgres supports this with the CLUSTER command, although it has to be run manually - it doesn't keep up as updates and inserts come in. SQL Server calls it a clustered index and enforces the physical ordering on inserts and updates. Oracle has indexed-ordered tables which can achieve a similar effect, though IIRC the index has to be the PK (nothing to stop you from having a unique constraint on a sequence-generated column, though.)

batbomb 12y ago
In oracle, the index on an index ordered table only needs be a unique. So, for example: 1234 "John Doe" null null null 5824 "John Doe" "Sale 1" "item 1" $5 4321 "John Doe" "Sale 1" "item 2" $50 4382 "John Doe" "Sale 2" "item 1" $4 CREATE UNIQUE INDEX (name, sale, item);