Re:Why do database columns have a character length of 191?
by TazeTSchnitzel on Monday May 17, 2021 @06:31PM
(#27189215)
Prefixes are a neat trick. One nice consequence is that an index for the column pair (a, b) is also an index for (a).
Re:Why do database columns have a character length of 191?
by branko_d on Tuesday May 18, 2021 @02:11PM
(#27198726)
And if the cardinality of a is low, it can even be an index for b! This is called “skip scan”.
Oracle has supported this for a long time, and apparently newer versions of MySQL as well.
1. They provide much better locality and updatability, because databases would historically get the data from extremely slow IO (spinning rust) btrees are a great fit, they're also extremely predictable (search, insert, and delete work in O(log n)).
2. hash indices only support exact matches, btrees support prefix and range matching, and they are ordered, so when all the stars align you don't even need to touch the data pages, you can serve an entire query by just reading the index.
Most databases allow opt-in hash indexes (and even other more complicated forms of indexes), but these are either much less flexible (hash) or much more expensive (gist/gin). The btree remains an excellent default.