source: npl/mailserver/dspam/cleanup.sql @ ac05c71

set @a=to_days(current_date());

delete quick from dspam_signature_data
  where @a-14 > to_days(created_on)
  AND uid > 1;

optimize table dspam_signature_data;

delete quick from dspam_token_data
  where (innocent_hits*2) + spam_hits < 5
  and @a-to_days(last_hit) > 60
  and uid > 1;

delete quick from dspam_token_data
  where innocent_hits = 1 and spam_hits = 0
  and @a-to_days(last_hit) > 15
  and uid > 1;

delete quick from dspam_token_data
  where innocent_hits = 0 and spam_hits = 1
  and @a-to_days(last_hit) > 15
  and uid > 1;

delete quick from dspam_token_data
USING
  dspam_token_data LEFT JOIN dspam_preferences
  ON dspam_token_data.uid = dspam_preferences.uid
  AND dspam_preferences.preference = 'trainingMode'
  AND dspam_preferences.value in('TOE','TUM','NOTRAIN')
WHERE @a-to_days(dspam_token_data.last_hit) > 90
AND dspam_preferences.uid IS NULL
AND dspam_token_data.uid > 1 ;

delete quick from dspam_token_data
USING
  dspam_token_data LEFT JOIN dspam_preferences
  ON dspam_token_data.uid = dspam_preferences.uid
  AND dspam_preferences.preference = 'trainingMode'
  AND dspam_preferences.value = 'TUM'
WHERE @a-to_days(dspam_token_data.last_hit) > 90
AND innocent_hits + spam_hits < 50
AND dspam_preferences.uid IS NOT NULL
AND dspam_token_data.uid > 1;

optimize table dspam_token_data;