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

Reply via email to