John Hansen wrote:
In reply, some hints for postgresql...:
Oct 23 11:42:28 localhost dbmail/imap4d[2762]: dbmysql.c,db_query:
executing query [SELECT message_idnr, seen_flag, recent_flag
FROM dbmail_messages WHERE mailbox_idnr = '9' AND status <
'2' AND unique_id != '' ORDER BY message_idnr ASC] Oct 23
11:44:19 localhost dbmail/imap4d[2762]: dbmysql.c,db_query:
executing query [SELECT MAX(message_idnr) FROM dbmail_
messages WHERE unique_id != '']
First of all,... REPLACE all occurences of 'MAX(whatever)' with
'whatever ORDER BY whatever DESC LIMIT 1'
The performance boost here can be as much as thousand fold with an index
on 'whatever'.
I like the way you guys are thinking.
The indexes for the second query also seem wrong, an index specifically
for this query would be required as follows:
CREATE INDEX messages_mbox_stat_uniq on
dbmail_messages(mailbox_idnr,status,length(unique_id));
I like the added index. Since I intent to drop unique_id from the queries, I
propose to add:
CREATE INDEX dbmail_messages_mailbox_status ON
dbmail_messages(mailbox_idnr,status);
and to mysql:
ALTER TABLE dbmail_messages ADD INDEX mailbox_status (mailbox_idnr, status);
So, the query [SELECT MAX(message_idnr) FROM dbmail_messages WHERE
unique_id != ''] becomes [SELECT message_idnr FROM dbmail_messages WHERE
unique_id != '' ORDER BY message_idnr DESC LIMIT 1]
If we drop the unique_id constraint the max() call is probably a little better for mysql. It would probably be
even better if we could extract the queries into a static runtime configuration, allowing administrators to
select the best queries for the backend at hand. For now however, a well-selected middle ground is optimal.
And the second query changed to: [SELECT message_idnr, seen_flag,
recent_flag FROM dbmail_messages WHERE mailbox_idnr = '9' AND status <
'2' AND length(unique_id) > 0 ORDER BY message_idnr ASC]
unique_id!='' should go where not absolutely required.
Any objections to removing that particular restraint from the code?
I really don't know how that one ever got in. If anyone happens to know, please speak or be silent for ever
more :-) In my database there are no messages with an empty unique_id. And there shouldn't be. Such sanity
checks shouldn't hog the real-time performance, and be deferred to asynchronous maintenance like dbmail-util.
I have this change secured in a dpatch allowing easy reversal of this change
should serious objections arise.
--
________________________________________________________________
Paul Stevens mailto:[EMAIL PROTECTED]
NET FACILITIES GROUP PGP: finger [EMAIL PROTECTED]
The Netherlands________________________________http://www.nfg.nl
