On 2010-11-02 18:28, James Cloos wrote: >>>>>> "PJS" == Paul J Stevens<[email protected]> writes: > > PJS> On 2010-11-02 07:17, James Cloos wrote: > PJS> Interesting self-join approach. But alas, though this works in sqlite > PJS> just fine, mysql-5.1 gives an error. > > Even something like (untested): > > BEGIN; > SELECT FOR UPDATE %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 ); > > DELETE FROM %sphysmessage WHERE id IN ( %s ); > COMMIT;
That's a pattern already used in the code - except for the 'FOR UPDATE' clause. Sometimes the IN sequence will have to be sliced up because the query buffer is static, and we don't want to have overrunning buffers. > using the results of the single select for the delete. > > Oh. And I see that I forgot to credit Tom Lane for helping me get the > syntax right. > > PJS> I'd love to see the patch for [fast list], or at least the > PJS> resulting query pattern. > > It is quite a bit more complicated. :) > > I also added support for LIST-STATUS, which resulted in this query: I've pulled your tree. I wasn't aware of this rfc -- it's not on any of the imapext pages I try to follow :-( nice job though. Are there any clients that actually support this? > (As you can see, I also included the U64_T_FORMAT patch from mantis.) > > The warn-llu denseuid fastlist and list-status branches of: > > git://people.freedesktop.org/~cloos/dbmail.git > > have the set of changes I currently use, in order. > > PJS> Though I very much agree on the principle, alas, the mysql version > PJS> involved is not so ancient. The actual limitation in place only > PJS> applies to 'delete from tableA where id in (select id from tableA > PJS> ...)', so there plenty of room for improvements. > > Getting the list in one go and then deleting them is not bad; it is just > the "get a probable list and then weed it down one at a time" paradigm > which quickly becomes achingly slow. Agreed. That old pattern should be replaced. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ Dbmail-dev mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
