I've noticed on MySQLWorkbench that you can choose how to store your indexes before forward engineering your design. The storage types are:


BTREE
RTREE
HASH


Researching this, I found some information that was pretty much over my head, so I'm looking for practical information on what the difference is between these and/or why you should choose one over another. 

Also, I have never chosen a storage type before, so I assume MySQL is choosing a default storage type (BTREE?)

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.
