On Wed, 2005-03-09 at 11:51, Thomas Mueller wrote: > Hi, > > I get more and more user complaints that my IMAP server is terrible > slow, opening a mailbox takes very long. > > I've analyzed that using PQA (http://pgfoundry.org/projects/pqa/, great > tool!), the output is attached if anyone is interested (includes all > databases, not only dbmail). > > The main problem is in db.c, db_getmailbox(): > SELECT COUNT(message_idnr), COUNT(message_idnr) - SUM(seen_flag), > SUM(recent_flag) FROM dbmail_messages WHERE mailbox_idnr = '5' AND > status < '2' > That query takes up to 15 seconds (!!). > > Obviously that doesn't scale at all - dbmail is not usable on PostgreSQL > with lot of mails and/or users. > > If I replace COUNT(message_idnr) with the value I get the query is about > 30 (!) times faster and takes about 0.4 seconds: > > SELECT 611, 611 - SUM(seen_flag), SUM(recent_flag) FROM dbmail_messages > WHERE mailbox_idnr = '5' AND status < '2'; > > So the solution is easy: add a row to dbmail_mailboxes that contains the > number of messages. The row is updated by a trigger when messages are > inserted or deleted. That is a quick and easy fix (only one query in the > dbmail code has to be changed) - but that doesn't work for MySQL, I know :-/ > > I think in this case database abstraction simply doesn't work. We have > to use two different queries in the code, but I might be wrong. Does > anyone have a better solution? > > > Thomas
I have not checked the schema, but if there is no index for count(message_idnr) then please try to add one. I don't know whether thisone will work, but it's worth a try? create index idx_test on dbmail_messages (mailbox_idnr, status, count(message_idnr)); DISCLAIMER: NOT TESTED, IT MIGHT WELL BLOW UP YOUR COKE CAN! Kind of busy today, but I might have another look later if nobody solves this. -HK
