Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?)

share|improve this question
add comment

1 Answer

up vote 18 down vote accepted

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 is can't be used to avoid sort operations, or to fetch ranges. Originally, MySQL only allowed HASH indexes on MEMORY tables; but i'm not sure if that has been relaxed.

share|improve this answer
    
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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.