michael.monnerie at is
Jan 16, 2009, 4:26 AM
Views: 322
Permalink
|
|
analyzed: moving messages via IMAP to another folder
|
|
I migrated my internal server from cyrus to dbmail 2.2 this week. I
increased postgresql logs to see what happens when you move a single
message from one mailbox to another. It's a total of 72 (!) SQL
commands: http://zmi.at/x/dbmail-move-message-2-another-folder.log
I did move the mail via dbmail. The following IMAP commands are run from
kmail when moving a single message:
593 UID FETCH 3458457 (UID RFC822.SIZE FLAGS BODY.PEEK[])
594 LIST "" "zzz_OLD/trash/trash 2007"
595 UID COPY 3458453 "zzz_OLD/trash/trash 2007"
596 UID STORE 3458453 "zzz_OLD/trash/trash 2007"
597 LIST "" "zzz_OLD/trash/trash 2007"
598 NOOP
599 SELECT "zzz_OLD/trash/trash 2007"
600 UID FETCH 3612741:* (UID RFC822.SIZE FLAGS ENVELOPE
BODY.PEEK[HEADER.FIELDS (REFERENCES)])
601 NOOP
Maybe this could be done a bit nicer, faster, both in kmail and dbmail.
I only looked into the SQL part. For example, the third command is a
SELECT COUNT(*) AS nr_children FROM dbmail_mailboxes WHERE owner_idnr =
1527 AND name ILIKE 'zzz\\_OLD/trash/%'
and you should NEVER EVER do count(*), because that makes the database
read the whole row of data. A "SELECT COUNT(1)" is much faster. There
are other COUNT(*) commands later, they could be optimized as well.
Then there are 2 commands directly neighboured that ask the same table,
but only a single data each. Could be made one SELECT:
SELECT owner_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = 3305
SELECT no_select FROM dbmail_mailboxes WHERE mailbox_idnr = 3305
Then the same for the message_idnr:
SELECT message_idnr+1 FROM dbmail_messages WHERE mailbox_idnr=3305 ORDER
BY message_idnr DESC LIMIT 1
SELECT message_idnr FROM dbmail_messages WHERE mailbox_idnr = 3305 AND
status IN (0,1) ORDER BY message_idnr
And why is there a SELECT with "message_idnr BETWEEN x AND y"? Doesn't
dbmail know the message_idnr exactly at that time?
SELECT seen_flag, answered_flag, deleted_flag, flagged_flag, draft_flag,
recent_flag, TO_CHAR(internal_date, 'YYYY-MM-DD HH24:MI:SS' ), rfcsize,
message_idnr FROM dbmail_messages msg, dbmail_physmessage pm WHERE pm.id
= msg.physmessage_id AND message_idnr BETWEEN 3458410 AND 3596775 AND
mailbox_idnr = 3305 AND status IN (0,1,2) ORDER BY message_idnr ASC
But I don't know if I'm wasting my time as Paul is on 2.3 and nobody
else is coding for dbmail. I'm willing to help optimize the SQL part,
maybe there's a coder out there who could create patches for Paul? Or
would it be better I test things with 2.3? Would Paul want to have that
done? Or is it too early and you still need to code "the big thing" and
there's no time to optimize anyway, and commands will still be changed
greatly?
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
|
analyzed: moving messages via IMAP to another folder
