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

  1. BTREE
  2. RTREE
  3. 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 (in fact B*Tree) is an efficient ordered key-value map. that means that a BTREE index can quickly find a record given the key, and it can be scanned in order. It also makes easy to fetch all keys (and records) within a range: "all events between 9am and 5pm", "last names starting with 'R'"

RTREE is an 'spatial index' that means that it can quickly identify 'close' values in 2 or more dimentions. Used in geographic databases for queries like "all points within X meters from (x,y)"

HASH is an unordered key-value map. It's even more efficient than BTREE: O(1) instead of O(log n); but it doesn't have any concept of order. That means that it can't be used for sort operations, or to fetch ranges. Originally, MySQL only allowed HASH indexes on MEMORY tables; but i'm not sure if that has been changed.

  • Does MySQL support Rtrees? – Pacerier Jul 5 '12 at 23:14
  • 2
    yes, they're called SPATIAL INDEX (dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html) – Javier Jul 5 '12 at 23:37
  • Cool, thanks =) Are there other structures besides these 3, or planned structures in the near future? – Pacerier Jul 6 '12 at 3:40
  • Memory tables support btree indexes as well – Amareswar Oct 20 '12 at 20:11
  • @Amareswar, right. Maybe my answer can be read both ways, but what i meant was that HASH indexes were only allowed on MEMORY tables, not on 'normal' tables. – Javier Oct 22 '12 at 14:05

protected by gnat Aug 27 '16 at 8:23

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?