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?

Improve this question

1 Answer 1

Reset to default
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?

Improve this answer
2
  • Yes, i want to know which cases someone want to use MEMORY instead of using other cache database ( redis, memcached , ... ). But can we control InnoDB cache ??? ( setting which query results should be saved, which columns, ... ) . So i think it may be better if we using redis or other databases to stores query results , and control flow between redis and mysql in our codebase
    – voxter
    Sep 13, 2017 at 3:29
  • Also consider whether you have embarked on "premature optimization". Develop a prototype without such a caching layer. If it proves to be in adequate, then talk about caching layers.
    – Rick James
    Sep 13, 2017 at 4:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.