Re: Auto increment

>
> People who have never measured the actual performance, claim that
> using it as a key to replace a multi-column key. Adding extra data
> that has to be read from a disk is far more expensive than comparing a
> long byte string in main storage. Do the math: your CPU runs in
> nanoseconds; your disk drive runs in milliseconds.
>

People who _did_ have measured sometimes also claim so. I did.
The natural primary key was on average 30 bytes. I replaced it with a 4-byte integer.
There were on average 50 children records and 200 grandchildren ones. Storage savings were 30-4=26 bytes per a row and same 26 bytes per a foreign key entry, so storage savings on average for children of one parent record only were 50*26*2=2600 bytes. Parent to child joins ran at least 20% faster.

Also some parent to child joins covered by indexes ran at least twice as fast. Received on Mon May 12 2003 - 20:17:31 CEST