0

In MySQL, hash indices are only supported by the MEMORY storage engine. By using this engine, data is stored in RAM, not on the hard disk, so it will be lost when MySQL is restarted.

Also, according to this link : hash index, it showed that hash indices are ineffective when data is stored on hard disk. But for practical usage for storing in RAM, Redis (other in-memory structures, .... ), may be a better choice than the MEMORY engine with MySQL.

So which use cases are valid for MEMORY storage engine and hash indices?

John K. N.
  • 18,854
  • 14
  • 56
  • 117
voxter
  • 101

1 Answers1

1

A BTree index is arguably better than a Hash index. A BTree can efficiently handle "ranges", whereas Hash is useless for such. For "point queries" they are about equivalent.

As for MEMORY, the allure is the speed of access. However, the caching of disk blocks makes any other Engine nearly as good in many production situations.

In a survey of over 1000 tables discussed on forums.mysql.com, only 6 were MEMORY. I suggest that says that MEMORY is only very rarely the 'engine of choice'.

The lack of persistence makes MEMORY a non-starter.

As a "cache", Redis, memcached, Memory, etc, have appeal. But InnoDB, for example, has a builtin cache, and it is usually inefficient to put a cache in front of a cache.

Perhaps you are thinking of some niche usage where MEMORY is well suited?

Rick James
  • 80,479
  • 5
  • 52
  • 119