Caching with Mysql

Simple caching app on top of Mysql

If you do not want to add additional techs to your stack (Mysql is in place, right?) but need caching, you can leverage Mysql great performance to cache your app slow parts. Quick example is over here…

Implementing caching storage on top of Mysql is easy. First of all, we have to implement key-value storage. After that we have to only add expiration functionality.

Add expiration column

In order to control keys expiration we’re going to use expire column:

CREATE TABLE `cache` (
  `key` varchar(32) NOT NULL PRIMARY KEY,
  `val` TEXT,
  `expire` timestamp
);

So when saving data for specific key we can set expiration time into expire column:

INSERT INTO cache
SET val = '{"name": "val"}', `key` = 'test', expire = NOW() + 60
ON DUPLICATE KEY UPDATE val = '{"name": "val"}', expire = NOW() + 60;

In this case our test key should expire in 60 seconds.

Enabling expiration

Now we return value for a specific key only if expire column value is more than current timestamp:

SELECT val FROM cache WHERE `key` = 'test' AND expire >= NOW();

That’s it. As well as key-value implementation we can leverage all Mysql benefits. This approach in implementation is used for Mysqly library caching component.