(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;