Why are all thoses mysql indexes not created in the dbmail cvs package Jacques
Paul Stevens a écrit : > 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 > > _______________________________________________ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail
