Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
It sounds like you're describing a queue of sorts. You might want to look at something like RabbitMQ –  atxdba Mar 31 at 21:41
    
@atxdba:You are almost right.Issue is that these kind of queues you are suggesting do not offer reliability –  Jim Mar 31 at 21:43
    
@atxdba:Also this is too simple to use a third party queue and not just a simple table (IMHO) –  Jim Mar 31 at 21:44
    
@atxdba:Reliability in the sense that items in the queue are not guaranteed to be lost –  Jim Mar 31 at 21:54
    
Previous comments are nonsense because they haven't actually asked you if the order everything is processed in matters. Also, rename when, it's a reserved word. You know your data - does it need processing in order? –  Phil Apr 1 at 0:26
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.