Warning: as described here, this is in many, many ways a hack and has lots to improve when it comes to optimization.

What are we up to?

Basically, we want a table that will store key-value pairs. We’re assuming your app will have more than one user, so the actual pairs are:

(item_key, item_value, owner_id, timestamp)

Create the table

CREATE TABLE `kv` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` INT(11) DEFAULT NULL,
  `item_key` varchar(128) DEFAULT NULL,
  `item_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `timestamp` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

You’ll of course want to adjust this to match your data, if, say, you’re using VARBINARY ids or some other cool thing. Note that the item_value column is ready for U+1F4A9 PILE OF POO. Now, now… no need to thank me.

Inserting pairs into the table

Where user 1340 sets theme.backgroundColor to #451120.

Inserting is fast, since no checks (e.g. there are no UPSERTs here) have to be made:

INSERT INTO kv VALUES (1340, "theme.backgroundColor", "#451120", 1474569150)

Querying the table

Where we pull the current values for all theme.* keys of user 1340.

This is the ~~wonderfully hacky~~ fun part. We’re going to LEFT JOIN the table on itself, to pull only the newest item_value for every requested item_key.

So let’s fetch the current pairs for all keys matching "theme.*".

SELECT kv.* FROM kv 
  LEFT JOIN _kv
  ON (
     kv.item_key = _kv.item_key AND
     kv.timestamp < _kv.timestamp
     AND kv.owner_id = _kv.owner_id
  ) WHERE _kv.id IS NULL
    AND kv.owner_id = 1340
    AND kv.item_key LIKE 'theme.%';

The purpose of the JOIN ON ( ... ) is to join every (item_key, item_value) pair with the pair it was overridden by (see kv.timestamp < _kv.timestamp). We later filter out (the WHERE _kv.id IS NULL part) all but the newest pair, since - being the newest pair - it will not have an overriding pair: _kv.timestamp will be NULL (it’s a LEFT JOIN after all).

Still with us? Great. The kv.owner_id = _kv.owner_id is to ensure we only join pairs from the same user.

Fetching multiple, explicit keys

Where we fetch theme.fontColor and theme.fontSize for user 1300.

The only part that changes is the WHERE clause:

SELECT ...
  LEFT JOIN ...
  ON ( 
    ...
  ) WHERE _kv.timestamp IS NULL
  AND kv.item_key IN ('theme.fontColor', 'theme.fontSize');

Cleaning up the trail

As time goes on, you’ll probably want to clean up older settings. Throw this on a cron and you’re good.

Optimizing indexes

We’ll look at optimizing this table (indexes, partitioning) in Part Two.