MySQL Variables for Consistent Querying

Introduction

This should be a short post, but I hope it’s still useful.

I found the need to ‘remember’ values in MySQL important as I have a website that shows the temperature of my house — every minute an Arduino feeds in new information to a database, and that database continues to grow. A problem arose after a few months: the queries I used to make this temperature-tracking website would take longer and longer. I didn’t want to completely erase the history I’d collected, but I needed a way to archive it from the central table that was used to generate the summary page.

Naive Approach

In my haste for the first few weeks, I ran something like this

INSERT INTO temperatures_history SELECT * FROM temperatures WHERE DATE_SUB(NOW(), INTERVAL 7 DAY) < timestamp;
DELETE FROM temperatures WHERE DATE_SUB(NOW(), INTERVAL 7 DAY) < timestamp;

Did you spot the problem? If in between running the first statement and the second, a record ‘ages out’, it might be DELETEd without having been INSERTed into the temperatures_history table first.

The Solution

-- Get maximum non-allowable ID
SET @c := (SELECT max(id) from temperatures WHERE DATE_SUB(NOW(), INTERVAL 7 DAY) > timestamp);

-- Move these old records over to the history table
INSERT INTO temperatures_history SELECT * from temperatures WHERE ID <= @c;

-- Delete these old records from the frequently-queried table
DELETE FROM temperatures WHERE ID <= @c;

This solution simply figures out the ID for ‘aging out’, and then queries upon that ID, establishing consistency across the two queries.