MySQL: Given a list of row values, how do you only return the changed rows?

Question

This is my first S/O question! I'm stumped on what seems to be a basic query; given a list of values for various rows, return only the values that have changed. I am building a live-updating feature for a ordering system and I need to update rows on the client browser that have changed since the last (5 second) polling interval. Since I'm dealing with thousands of rows I need to only update what's changed.

Here's what my table looks like:
id, order_number, department

My client sends a list of two values (x1000+ items):
order_number, department

'department' is the value that I'm looking to see if a change has occurred.

I need a way to query the DB once with the entire list from the client and have MySQL return a list of only the changed items. Given a list of 1500 orders, it's likely that only one will ever be changed at a time, so I think for efficiency it makes sense to have the DB engine do this for me rather than looping through the list and doing a query each time. I tried doing it with a temp table and while it kind of worked, I found that the DROP TABLE at the end precluded my query results from being returned:

CREATE TEMPORARY TABLE `orderlist` (
    `order_number` varchar(128) DEFAULT NULL,
    `department` int(11) DEFAULT '1'
);
INSERT INTO orderlist (order_id, department) VALUES ('180425','2');
INSERT INTO orderlist (order_id, department) VALUES ('180426','2');
INSERT INTO orderlist (order_id, department) VALUES ('180427','3');
SELECT orders.order_number, orders.department
FROM orders
JOIN orderlist USING(order_number)
WHERE orderlist.department != orders.department;
DROP TABLE orderlist;

Answer 1

One solution could be to add a last_updated timestamp on update current_timestamp column to your orders table. So when an update happens, this column is automatically updated by MySQL to contain the current time.

When you query the database, use the current time minus 5 seconds to compare against the last_updated column i.e.

where last_updated > now()-5

This will give you only the orders updated in the last 5 seconds.

Make sure you add an index on the new last_updated column.