Menu
Since MySQL uses BTREE by default when creating an index, is there some instance when I can use HASH? For example, if my table only consists of Foreign Keys which are just INT UNSIGNED values. Is it a good improvement to override BTREE with HASH in this case?
Not sure if it matters, but I'm using InnoDB.
470
Hash indexes, on the other hand, do not store the actual key value, only the 4-byte signed hashed value of the key. One last advantage hash indexes enjoy over B-Tree indexes is hash index size isn't affected by how selective the index key value is.
If you are 100% certain that a column will only ever need to be looked up via direct equality (eg... where id = X), then you can use a hash index. Even in that case, unless that table is at the core of your business and is becoming a bottleneck for system performance in general, I would just use a B+ tree anyways.
In BST we can do range searches efficiently but in Hash Table we cannot do range search efficienly. BST are memory efficient but Hash table is not.
Hash. Hash is an unordered key-value map. It's even more efficient than a BTree: O(1) instead of O(log n) . But it doesn't have any concept of order so it can't be used for sort operations or to fetch ranges.
The HASH index type is only supported for MEMORY (aka HEAP) storage engine.
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
