> -----Messaggio originale----- > Da: [EMAIL PROTECTED] [mailto:amavis-user- > > On Aug 17, 2007, at 7:01 PM, [EMAIL PROTECTED] wrote: > >> Please forgive me for replying to my own message here, but I'd > >> like to share some information about some significant progress I've > >> just made. I managed to catch one of amavisd-new's SQL transactions > >> in MySQL's "show full processlist" output, and it was executing an > >> update query on the msgs table with a search field (mail_id) that > >> wasn't indexed. I added a new index on that field thusly: > >> > >> CREATE INDEX msgs_mail_id ON msgs (mail_id); > >> > > On my setup mail_id is the primary key and MySQL automatically adds > > a unique index for the primary key. > > > > Adding another index for mail_id will not do anything bad except: > > - You added a redundant index > > - You need more space on the database (for the redundant index) > > - Inserting data to the database will take more time (maintaining > > indices is time intensive) > > Yup, that makes sense to me. > > A "show index ..." didn't list an index on that field, which > suggests that it wasn't defined as a primary key in the original > CREATE TABLE statement. I double-checked the CREATE TABLE statements > that I used to set up this database, and sure enough, there was no > primary key defined.
I guess it's better you don't use the mail_id field as a primary key: it may not be unique in your db. Think, in example, to messages sent to destinations defined as a list or an alias in your system. Giampaolo > > The flood has died down and my mail server is now keeping up. > I've also tuned the MySQL server a bit (adjusted some buffer sizes in > my.cnf) and reduced the query latency a bit further; most queries are > now executing in less than 10ms. > > -Dave > > -- > Dave McGuire > Port Charlotte, FL > Farewell Ophelia, 9/22/1991 - 7/25/2007 > > > > > ----------------------------------------------------------------------- > -- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > _______________________________________________ > AMaViS-user mailing list > AMaViS-user@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/amavis-user > AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 > AMaViS-HowTos:http://www.amavis.org/howto/ ------------------------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ _______________________________________________ AMaViS-user mailing list AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
