On 2010-11-02 07:17, James Cloos wrote: > 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:
Interesting self-join approach. But alas, though this works in sqlite just fine, mysql-5.1 gives an error. The fine manual reads: '''Currently, you cannot delete from a table and select from the same table in a subquery.''' And it looks like this hasn't been fixed in 5.5. > 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.) I'd love to see the patch for that, or at least the resulting query pattern. > > 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. Though I very much agree on the principle, alas, the mysql version involved is not so ancient. The actual limitation in place only applies to 'delete from tableA where id in (select id from tableA ...)', so there plenty of room for improvements. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
