I don't know whether this works with mysql or sqlite, but for pgsql the best way to delete the physmessage rows for which the referring messages rows all have status=3 is:
DELETE FROM %sphysmessage WHERE id IN ( SELECT %smessages.physmessage_id FROM %smessages LEFT JOIN ( SELECT physmessage_id FROM %smessages WHERE status < 3 ) m2 ON ( %smessages.physmessage_id = m2.physmessage_id ) WHERE m2.physmessage_id IS NULL AND %smessages.status = 3 ); It will use indices -- specifically the btree (physmessage_id) and btree (status) indices -- rather than sequential scans, and takes just a single SELECT rather than one for the list of status=3 physmessage_ids plus one per such id to ensure that there are no additional status<3 links to that id. The reduced memory and disk i/o pressure will have a significant improvement on client access when the hourly runs. There are a number of other areas where dbmail can improve by actually /using/ sql. I was able impove imap LIST from approximately 1k seconds for a 20k row result to less than one second, just by doing it in one sql SELECT. (Same idea as above.) Presumably because it was first written for a, by now, ancient version of mysql, dbmail makes frequent use of the idiom of doing an intial SELECT and then, for each row that first SELECT returns, doing one or more additional SELECTs. Instead it should strive to always use a single SELECT for each supported IMAP command. It makes everything more efficient; significantly more efficient as the result set grows. -JimC -- James Cloos <cl...@jhcloos.com> OpenPGP: 1024D/ED7DAEA6 _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev