DataDenys
Published in

DataDenys

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Denys Golotiuk

Denys Golotiuk

Data-intensive apps engineer, tech writer, opensource contributor @ github.com/mrcrypster