Wonder if I can do this.. (hard to explain in title)

[awpti]

Howdy, everyone.

 

I've got the following table structure;

 

CREATE TABLE domain_runner (
   `id` BIGINT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT
   `parent_url` TEXT,
   `url` TEXT,
   `links_found` INT,
   `status` ENUM ('n', 'q', 'p'),
   `time_stamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

 

I'm selecting X amount of items from the table WHERE status = 'n', eg;

 

SELECT * FROM domain_runner WHERE status = 'n' LIMIT 500;

 

Once I'm done selecting them (or while), I'd like to set the status to 'q'.

 

Can I do this within MySQL or will I need to handle it in my script?

 

It'd be fairly trivial to do it in a script, but letting MySQL handle it seems a much smarter proposition.

[corbin]

Two suggestions not particularly related to the answer:

 

-Perhaps you have it and you just didn't show it, but if you're using status as a search criteria, you should have an index on it.

-ENUMs should typically be avoided.  Perhaps use a TINYINT instead or something.  (Well, in this case it would probably be harmless...  ENUMs are really only a problem if there are like 123981490 options.)

 

 

 

Anyway:

 

UPDATE domain_runner SET status = 'q' WHERE status = 'n' LIMIT 500;

[awpti]

There is a possible contention issue with that.

 

The domain runner script I'm creating has a parent process and then between 2 and 5 child processes that are launched which do the actual processing.

 

I guess the only real way to do it would have the script pull all the ids from the list of domains gathered and set it this way, perhaps?

 

UPDATE `domain_runner` SET `status` = 'q' WHERE `id` IN (...);