What is the difference between btree and rtree indexing?
52
BTree
BTree (in fact B*Tree) is an efficient ordered key-value map. Meaning:
- given the key, a BTree index can quickly find a record,
- a BTree can be scanned in order.
- it's also easy to fetch all the keys (and records) within a range.
e.g. "all events between 9am and 5pm", "last names starting with 'R'"
RTree
RTree is a spatial index which means that it can quickly identify close values in 2 or more dimensions. It's used in geographic databases for queries such as:
all points within X meters from (x,y)
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.
As a side note, originally, MySQL only allowed Hash indexes on MEMORY tables; but I'm not sure if that has been changed over the years.
