Re: [PHP-DB] MySQL circular buffer
> (i.e. stack_id > 500 && stack_id < 601 vs where stack_id = 500 limit 100) stack_id between 501 and 600 (stack_id > 500 && stack_id < 601) is much better What I would like to know is if anybody has experience implementing this sort of data structure in MySQL (linked list?) or any advice. tables: process_table: IDProcess PK mail_table IDMail PK IDPrrocess FK references process_table.IDProcess ON DELETE SET NULL Mail TEXT From VCH(255) TO VCH(255) DateModified DATE ON UPDATE CURRENT_TIMESTAMP Mailed TINYINT DEFAULT 0; -- code: define('MaxProccessTimeMinutes', 30); define('BatchCount', 100); INSERT INTO process_table VALUES (); $lnIdProcess = GetLastIDProcess(); label send_mail: $ldDateExpired = time() - MaxProccessTimeMinutes * 60; UPDATE mail_table SET IDProcess = $lnIdProcess WHERE Mailed = 0 AND ( IDProcess IS NULL OR ( IDProcess IS NOT NULL AND DateModified <= $ldDateExpired ); ) LIMIT BatchCount; while ($result = SELECT IDMail, MailText, From, To FROM mail_table WHERE IDProcess = $lnIDProcess) { if (send_mail()) { UPDATE mail_table SET Mailed = 1 WHERE IDMail = $result['IDMail']; } } if there are other mails goto send_mail; else DELETE FROM process_table WHERE IDProcess = $lnIDProcess; this way more than one process can send mails, also if one process exits prematurely the other can send his emails later. Time to time run cron: DELETE FROM mail_table WHERE Mailed = 1; rebuild indexes on mail_table; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL circular buffer
Hello, I'm looking at implementing a database-level stack for a (multi stack) mail merge queue (able to queue up to 1 million messages per stack). Current workflow: server 1 (containing main db) transmits data sets (used to populate mail merge template) to server 2 server 2 web-facing script (script 1) puts data sets onto stack (db) server 2 mail process script (script 2) pulls single data block (say 100 rows) from front of stack (fifo), merges the data with the template and sends data to smtp process server 2 script 2 removes "processed" block of rows The problems I am considering include keeping track of the value of the primary key across multiple instances of script 2 (the script will run from a cron), whether to select by limit or range (i.e. stack_id > 500 && stack_id < 601 vs where stack_id = 500 limit 100) and looping the index back to zero while ensuring there is no data that hasn't been deleted. So - it seems easier to avoid these problems and implement a circular buffer :) What I would like to know is if anybody has experience implementing this sort of data structure in MySQL (linked list?) or any advice. There don't seem to be any current implementations so the last question is - is there a good reason for that? Too many overheads? I know this sort of structure is best kept in memory and not on disk, but I am not sure of any other solution to a queue this size. Any comments welcome. Many thanks, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php