> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf > Of Dave McGuire > Sent: Friday, August 17, 2007 5:43 PM > To: amavis-user@lists.sourceforge.net > Subject: Re: [AMaViS-user] excessive time spent in sql-update? 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: > Upgrade to amavisd-new 2.5.2 if you can, it also has updated documents and some optimizations on mysql tables (don't do the FK stuff.. See some of the latest discussions on mysql tables and Fkeys
> CREATE INDEX msgs_mail_id ON msgs (mail_id); Interesting, I think we create that at 'not null primary key', so I would have thought it would have an index on it already. CREATE TABLE msgs ( mail_id varchar(12) NOT NULL PRIMARY KEY, -- long-term unique mail id .... > > ...and the average (eyeballed) query times have dropped from 8-15 > seconds to less than 100ms, in some cases less than 20ms. > > I'm currently digging for the documentation I used to > create those > tables to see if I missed the need for that index. > In ../share/doc/amavisd-new/README.sql-mysql CREATE TABLE msgs ( mail_id varchar(12) NOT NULL PRIMARY KEY, -- long-term unique mail id > Can anyone share any other tips here? What other indices are > people using to improve query performance on their database-backed > amavisd-new installations? > > -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 email has been scanned and certified safe by SpammerTrap(tm). For Information please see http://www.spammertrap.com _________________________________________________________________________ ------------------------------------------------------------------------- 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/
