Aaron Stone wrote:
You would do well to look back in the archives and find the appropriate
set of indices to add into the database. My system's performance increased
dramatically by adding a few well places indices!
Here's the README.Debian contained in the debian packages.
dbmail for Debian
-----------------
This package was build from the cvs version. The only patch was for
dbmail.conf to move from /etc/dbmail.conf to /etc/dbmail/dbmail.conf.
The debian package also runs dbmail-smtp as user dbmail via suid. This
will allow normal users to insert mail through dbmail-smtp (i.e. from
procmail) without granting them access to dbmail.conf and the password
contained therein.
Please read the INSTALL file for instructions.
Optimizing your tables
-----------------
I found dbmail to become increasingly useless without additional
indexes. Speed increases as dramatically as server load drops
after adding these.
mysql:
alter table mailboxes add index (name);
alter table mailboxes add index (owner_idnr);
alter table mailboxes add index (is_subscribed);
alter table messages add index (mailbox_idnr);
alter table messages add index (seen_flag);
alter table messages add index (unique_id);
alter table messages add index (status);
postgresql:
create index name_idx on mailboxes(name);
create index owner_id_idx on mailboxes(owner_idnr);
create index is_subscribed_idx on mailboxes(is_subscribed);
create index mailbox_id_idx on messages(mailbox_idnr);
create index seen_flag_idx on messages(seen_flag);
create index unique_id_idx on messages(unique_id);
create index status_idx on messages(status);
For people using a database that supports foreign keys some additional
contraints will be usefull. Some examples that I applied to innodb
tables. Please note that 'on delete cascade' is not supported on
mysql/innodb before 3.23.50. Following statements where tested on
postgres as well.
alter table messages add foreign key (mailbox_idnr) references
mailboxes(mailbox_idnr) on delete cascade;
alter table mailboxes add foreign key (owner_idnr) references
users(user_idnr) on delete cascade;
alter table messageblks add foreign key (message_idnr) references
messages(message_idnr) on delete cascade;
--
________________________________________________________________
Paul Stevens mailto:[EMAIL PROTECTED]
NET FACILITIES GROUP PGP: finger [EMAIL PROTECTED]
The Netherlands________________________________http://www.nfg.nl
