Hi all,
Allow me to bounce some thoughts.
Working on the looping imap daemon I ran into a known problem: keeping the cached mailbox state in sync with
the database. http://www.dbmail.org/mantis/bug_view_advanced_page.php?bug_id=0000122
I'm looking into making db_getmailbox as cheap and fast as possible while still remaining reliable, so I can
throw it in where-ever the mailbox state is accessed, without having to worry about performance too much.
Currently db_getmailbox does:
/* count messages */
snprintf(query, DEF_QUERYSIZE,
"SELECT COUNT(message_idnr), COUNT(message_idnr) - SUM(seen_flag),
SUM(recent_flag) "
"FROM dbmail_messages WHERE mailbox_idnr = '%llu' "
"AND status < '%d' ",
mb->uid, MESSAGE_STATUS_DELETE);
[...]
/* get messages */
if (mb->exists) {
snprintf(query, DEF_QUERYSIZE,
"SELECT message_idnr FROM dbmail_messages WHERE mailbox_idnr
= '%llu' "
"AND status < '%d' ORDER BY message_idnr ASC",
mb->uid, MESSAGE_STATUS_DELETE);
I'm suggesting it's worth the effort to add a timestamp field to the mailbox table (and struct) which will
allow us to detect the last-modified time on a mailbox, i.e:
/* get mtime */
snprintf(query, DEF_QUERYSIZE,
"SELECT mtime FROM %smailboxes "
"WHERE mailbox_idnr = '%llu'", DBPFX, mb->uid);
Thus, we can avoid the 'SELECT COUNT()..' and 'SELECT message_idnr...' parts unless the cached timestamp for
the mailbox is less than what came out just now.
This will help bigtime if only in memory usage, but some second guessing by
others would be nice.
Like how to best do this in mysql and postgresql.
for mysql I'd do something like:
Add a TIMESTAMP field called mtime to dbmail_messages, and a normal DATETIME
called mtime to the mailboxes table.
/* update mtime */
snprintf(query, DEF_QUERYSIZE,
"SELECT MAX(mtime) FROM dbmail_messages WHERE"
"mailbox_idnr = '%llu'", mb->uid);
[...]
/* if mtime has changed */
snprintf(query, DEF_QUERYSIZE,
"UPDATE dbmail_mailboxes set mtime='%s' WHERE mailbox_idnr =
'%llu'",
mtime, mb->uid);
but for postgresql a trigger setup would be sufficient, right?
--
________________________________________________________________
Paul Stevens mailto:[EMAIL PROTECTED]
NET FACILITIES GROUP PGP: finger [EMAIL PROTECTED]
The Netherlands________________________________http://www.nfg.nl
