Hi, all. First of all, thanks for all the help on my earlier questions. This list has been incredibly helpful.
I've broken out some data into two tables for speed: a and b. a has fixed columns and sees only SELECTs and INSERTs, while b has several TEXT fields and sees SELECTs, INSERTs, and UPDATEs. Once a minute, several processes crawl b and update its TEXT fields. Due to the long-running nature of these processes, it's likely that they'll overlap, so I need to be very careful with the UPDATEs. I'm using MySQL 4.0.17, and am avoiding InnoDB, so I'm doing the following: LOCK TABLES b WRITE, a READ LOCAL; SELECT a.id FROM a LEFT JOIN b ON b.id = a.id WHERE a.type = 'foo' AND a.action = 'T' AND b.status IS NULL; Then I loop through the ids (in perl), marking each row in b with a 'Q' for 'queued', so an overlapping run doesn't try to re-proccess the same row: UPDATE b SET status = 'Q' WHERE id = ?; UNLOCK TABLES; Finally, I update the TEXT fields in b for the ids returned by the SELECT. Obviously, these locks can be held for quite a while if that SELECT is large, so is this The Right Way To Do It, or can I somehow combine the SELECT and the update into a single step, returning a list of ids? Speed is a big concern, so if I can do away with the locking, it'd be a huge win. I can't perform subselects with 4.0, and I can't imagine table replacement mentioned in the Cookbook (pp.647-648) is a good candidate in this scenario, so maybe what I'm doing is the only way until I get over my fear of deadlocks in InnoDB. ____________________________________________________________ Eamon Daly NextWave Media Group LLC Tel: 1 773 975-1115 Fax: 1 773 913-0970 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
