Why not use MySQL like memcached?

Question

For the same reason it makes sense to use both NoSQL and RDBMSs in one application, It makes sense to me to have an RDBMS cache besides NoSQL cache.

I was thinking about using a MySQL server with all tables using the memory engine.

Is there any caveat to this approach?

Just to clarify, I am suggesting here to use a MySQL server for caching purposes only, not for the actual data storage of my application.

Answer 1

Memory tables are stored entirely in memory, so it is very fast.

It uses hash indexes which are also very fast, great for temp table purposes and lookups.

Memory tables have table level locks, so if concurrency is required, this is a problem

No transactions

When server shuts down or crashes ALL ROWS ARE LOST

though table definition stays the same, the data would be all gone.

You may want to check out the official documents on the Memory Engine

EDIT:

The Memory Storage Engine is a good candidate for caching purposes.

The following are a few things that the Memory Engine is good for:

• Lookup/mapping tables
• caching results of periodically added data
• for data analysis purposes
• Sessions management
• Low latency operations
• Better than other strategies such as CREATE TEMPORARY TABLE as the Memory table persists (if that is what you need)

There are a few negatives:

• It does not support TEXT or BLOB columns, the table would be converted to a MyISAM on disk if such an event happens.
• The table should not hold too much data, as it takes up resources that can otherwise be allocated to indexes/query caches

All in all the memory engine should be the best choice for you if you need caching.

Answer 2

another reason,
RAM is much limited compared to disk space,
you can have disk drive up to terabytes,
but hardly can let memory goes up-to terabytes

Answer 3

If you got the $$, the following article about MySQL and InnoDB's capabilities will be of high interest.

It outperforms any sort of cache, memory engine or memcached. Drawback is - it requires ram, lots of it.