What I need is a simple process that handles requests.
I have setup a simple table as follows:
CREATE TABLE Requests ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, request_param VARCHAR(250) NOT NULL, status ENUM ('requested', 'done', 'failed'), when TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, finished TIMESTAMP NULL );
What I am doing is something like:
SELECT * FROM Requests where status = 'requested' ORDER BY id LIMIT 1
This way I pick up each request one at a time.
Then I do
UPDATE Requests set status='done', finished=NOW() where id = 123;
(the id=123 assume it is from the SELECT request).
This works but I am not sure if this is another approach.
What would be better? E.g. doing a LIMIT 100? My concern is that the order by is not a good idea as the data grow. Does it make an impact if I always process e.g. per 100? I still have to order by right?