4 ms·
Conversely, certain queries can be much faster by using natural keys when the FK is all that you need in the result rather than additional fields in the primary
by mypalmike 2y ago
Conversely, certain queries can be much faster by using natural keys when the FK is all that you need in the result rather than additional fields in the primary table. In this case, the primary table doesn't need to be queried at all. This doesn't generally overcome the benefits of synthetic keys, but it is an optimization sometimes put into practice.
- crabmusket 2y agoAren't you literally describing an index?
- mypalmike 2y agoNo. Maybe an example helps. You have a users table with username as natural PK. You have an access table with timestamps of users hitting a service, with FK of username. If you query the access table for a range of timestamps and just want the usernames, they're right there in the results of the access table query. If you had instead used a synthetic user_id key, the db would have to do an additional lookup in the users table via a join for each user_id to get the usernames.
- crabmusket 2y agoOh of course, that makes sense. I'm not sure how I misunderstood that!