Sitemap
DataDenys

Technical articles on creating, scaling, optimizing and securing big data applications

Follow publication

Caching with Mysql

Simple caching app on top of Mysql

2 min readAug 5, 2022

--

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.

--

--

DataDenys
DataDenys

Published in DataDenys

Technical articles on creating, scaling, optimizing and securing big data applications

Denys Golotiuk
Denys Golotiuk

Written by Denys Golotiuk

I write about data, machine learning, and its practical implementation. github.com/mrcrypster https://datachild.net golotyuk@gmail.com

No responses yet