Hello,
On 8/17/07, Nate <[EMAIL PROTECTED]> wrote:
>
> Just for reference, the queray, down from 3 hours
> when using foreign keys.ies I'm doing to clean the database now
> with foreign keys removed in mysql. I believe this is catching
> everything, and taking roughly 7 minutes per d
>
> # clear msgs table for anything older than $days_quarantine
> DELETE LOW_PRIORITY FROM msgs WHERE time_num < UNIX_TIMESTAMP() -
> $days_quarantine*24*60*60
Maybe get the value of
UNIX_TIMESTAMP() - $days_quarantine*24*60*60
assigned to a variable before executing the query to avoid calculating
UNIX_TIMESTAMP() for each scanned row.
> # delete anythign that's not quarantined longer than we keep data for penpals
> DELETE LOW_PRIORITY FROM msgs WHERE quar_type != 'Q' AND (time_num <
> UNIX_TIMESTAMP() - $penpal_days*24*60*60)
!= is very slow operator, testing the nonexistance of a value can lead
to full table scan in many cases. Change it to
IN (' ','F','Z','B','Q','M','L')
this should work much faster.
And apply the above note for the
UNIX_TIMESTAMP() - $penpal_days*24*60*60
expression.
> # clean maddr table from orphaned records
> DELETE LOW_PRIORITY FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs
> WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id)
NOT EXISTS is another slow operator. Try to change the query to:
DELETE maddr FROM maddr LEFT OUTER JOIN msgs ON sid=id LEFT OUTER JOIN
msgrcpt ON rid=id WHERE sid IS NULL OR rid IS NULL;
This should execute much faster.
> # clean msgrcpt table from orphaned records
> DELETE LOW_PRIORITY FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs
> WHERE mail_id=msgrcpt.mail_id)
Try to change to:
DELETE msgrcpt FROM msgrcpt LEFT OUTER JOIN msgs ON
msgrcpt.mail_id=msgs.mail_id WHERE msgs.mail_id IS NULL;
> # clean quarantine table from orphaned records
> DELETE LOW_PRIORITY FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM
> msgs WHERE mail_id=quarantine.mail_id)
DELETE quarantine FROM quarantine LEFT OUTER JOIN msgs ON
quarantine.mail_id=msgs.mail_id WHERE msgs.mail_id IS NULL;
Regards,
Andrzej
-------------------------------------------------------------------------
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/
