While writing yet another rails migration I was performing my standard 'ok, how is the lookup going to work & what should be indexed' check. Which was pretty easy since this table contained foreign keys. I added the index, which defaulted to
btree and I wondered why.
btree indexes are your only choice for range queries, but this is a foreign key lookup.
hash indexes are optimized for exactly this type of equality operation.
Then I found it, in a user comment in mysql's docs:
InnoDB and MyISAM do not support HASH indexes. HASH is syntactically recognized, but silently replaced by BTree.
In addition, when using
hash, the query must be for the entire index key, whereas
btree can be looked up by the leftmost value. So for chained indexes, btree is the way to go.
source: mysql docs