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.
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.
-- 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.