3 ms·
> What I don't quite udnerstand however, is why is sorted random better if I'm using an index in both cases? Data locality. The default index in most RDBMS is
by masklinn 6y ago
> What I don't quite udnerstand however, is why is sorted random better if I'm using an index in both cases?
Data locality. The default index in most RDBMS is a btree which is ordered and clustered so when you look up items in an index if they sort similarly they’ll be in the same index page(s) and thus cheaper to retrieve. For PKs that means when you go through your records it’s cheap to get siblings.
When the ids are randomly distributed it’s way more expensive to add them because they get added at random positions (writes are scattered which is bad) and when you retrieve them you have to jump through the index at random loading a bunch of pages to get just one or two records from it.
Things are even worse if the table itself is clustered after the PK because now your scattered index is also ducking with your actual data reads.