I don't expect to get an explanation for why this received downvotes - but maybe someone can offer one?
My best theory is that it's the Reddit "I don't need to understand how stuff works to be a programmer" crowd.
My best theory is that it's the Reddit "I don't need to understand how stuff works to be a programmer" crowd.
Generally 1 index contains the data, all the other indexes have pointers to that index in their leaf nodes. Usually the PK index contains the data
So loading 1000 consecutive rows in a created_at secondary index takes two steps: 1) created_at index B-Tree traversal (maybe 10 pages from disk, max?) 2) Potentially loading 1000 randomly sorted pages from disk, dereferencing pointers (EXPENSIVE!)
Whereas, if your primary key is sorted by time, loading 1000 rows is 1) slightly larger primary B-Tree traversal 2) no 2, you're done :)