Thought Experiment: Splitting A Single Data Table Up Into High-Writes And High-Reads For Better Performance In MySQL

snippet1.sql

UPDATE
	user u
SET
	u.lastRequestAt = UTC_TIMESTAMP()
WHERE
	u.id = ?
;

SELECT
	u.id,
	u.name,
	u.email,
	u.lastRequestAt
FROM
	user u
WHERE
	u.id = ?
;

snippet2.cfm

<cfscript>
	
	// Read the session from Redis.
	userSession = redis.hgetall( "sessions:#userID#:#sessionToken#" );

	// Optimistically update the in-memory value for presence.
	userSession.lastRequestAt = now();

	// ASYNCHRONOUSLY log the request timestamp to both Redis and MySQL.
	runAsync(
		() => {

			// Update the write-through cache.
			redis.hset(
				"sessions:#userID#:#sessionToken#",
				"lastRequestAt",
				userSession.lastRequestAt
			);

			// Update the underlying database record.
			userPresenceGateway.update({
				userID: userSession.id,
				lastRequestAt: userSession.lastRequestAt
			});

		}
	);

</cfscript>