3 ms·
I also like to use this coin toss analogy as one of my arguments against the use of Type4 GUID keys. Consider a hypothetical: SQLite is to be used to mediate th
by bob1029 6y ago
I also like to use this coin toss analogy as one of my arguments against the use of Type4 GUID keys. Consider a hypothetical: SQLite is to be used to mediate the monitoring, arming and detonation of a thermonuclear warhead. Would you want to use Type4 GUID keys or integer keys for the arming sequence table? Why not just take that extreme as the normal, and use boring & safe approaches everywhere? Assume everything can explode in horrible ways that you could never hope to anticipate. All you really need to do is make sure the explosion is impossible within your problem domain, or is at least manageable if it does occur.
An observation I have happened upon recently: The more time you spend away from the computer thinking about the problem you want to solve with the computer, the better the outcome will be. Spending a few days to consider the pros/cons of various business entity identity schemes is probably worthwhile when weighing the long term consequences.
- barrkel 6y agoA better argument against Type 4 GUID keys is that random distribution of your primary key doesn't preserve temporal locality in indexes. If you have a big enough keyspace to worry even in the slightest about collisions in 122 bits, you have a bigger index than will comfortably fit in memory. Under those circumstances, random key lookups will thrash your cache. If you access multiple rows at a time which were allocated close together in time - not unusual with normalized schema - you'll want them adjacent in the index too.
- bob1029 6y agoAny reasonable database index implementation will utilize data structures which address this exact flavor of concern. Concepts like the splay tree can optimize the locality of access dynamically based upon actual access patterns over time. But, you are right in that this locality of access property is available by default using integral types without the constraint imposed of needing to use a particular kind of dynamic data structure.
- barrkel 6y agoPlenty of databases use b+trees, which don't address this exact flavour of concern. In my direct experience, MySQL will perform quite poorly with lots of random lookups once your index starts getting to a meaty size.