>>>>> "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;
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:
if (only_subscribed) {
lsub_join = g_strdup_printf("LEFT JOIN %ssubscription sub ON
sub.mailbox_id = mbx.mailbox_idnr ", DBPFX);
lsub_suid = g_strdup_printf("sub.user_id = %" U64_T_FORMAT " ",
user_idnr);
} else {
lsub_join = g_strdup("");
lsub_suid = g_strdup("1=1 ");
}
snprintf(query, DEF_QUERYSIZE,
"WITH "
"exists AS (SELECT count(*) AS exists, mailbox_idnr FROM
dbmail_messages WHERE status < 3 GROUP BY mailbox_idnr), "
"seen AS (SELECT count(*) AS seen, mailbox_idnr FROM
dbmail_messages WHERE (status < 3 AND seen_flag=1) GROUP BY mailbox_idnr), "
"recent AS (SELECT count(*) AS recent, mailbox_idnr FROM
dbmail_messages WHERE (status < 3) AND recent_flag=1 GROUP BY mailbox_idnr) "
"SELECT distinct(mbx.name), mbx.mailbox_idnr, mbx.owner_idnr,
mbx.no_select, mbx.no_inferiors, mbx.children, "
"mbx.permission, mbx.seen_flag, mbx.answered_flag,
mbx.deleted_flag, mbx.flagged_flag, mbx.recent_flag, mbx.draft_flag, "
"mbx.next_uid, "
"exists.exists, seen.seen, recent.recent "
"FROM %smailboxes mbx "
"LEFT JOIN %sacl acl ON mbx.mailbox_idnr = acl.mailbox_id "
"LEFT JOIN %susers usr ON acl.user_id = usr.user_idnr "
"%s "
"LEFT OUTER JOIN exists ON exists.mailbox_idnr =
mbx.mailbox_idnr "
"LEFT OUTER JOIN seen ON seen.mailbox_idnr = mbx.mailbox_idnr "
"LEFT OUTER JOIN recent ON recent.mailbox_idnr =
mbx.mailbox_idnr "
"WHERE %s (%s "
"AND ((mbx.owner_idnr = %" U64_T_FORMAT ") "
"%s (acl.user_id = %" U64_T_FORMAT " AND acl.lookup_flag = 1) "
"OR (usr.userid = '%s' AND acl.lookup_flag = 1)))",
DBPFX, DBPFX, DBPFX, lsub_join, matchname,
lsub_suid, search_user_idnr,
search_user_idnr == user_idnr?"OR":"AND",
user_idnr,
DBMAIL_ACL_ANYONE_USER);
Just doing LIST as a single SELECT doesn't require quite as much data,
but it is still blazing fast.
(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.
-JimC
--
James Cloos <[email protected]> OpenPGP: 1024D/ED7DAEA6
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
