On Sat, 19 Dec 2009 14:30:28 +0100 (CET)
"Nicolas Grekas" <nicolas.gre...@espci.org> wrote:

> --
> -- Delete unused tokens, except for TOE, TUM and NOTRAIN modes
> --
> START TRANSACTION;
> DELETE LOW_PRIORITY QUICK
>   FROM t USING dspam_token_data t
>     LEFT JOIN dspam_preferences p ON p.preference = 'trainingMode' AND p.uid =
> t.uid
>     LEFT JOIN dspam_preferences d ON d.preference = 'trainingMode' AND d.uid 
> = 0
>   WHERE from_days(@tod...@purgeunused) > last_hit
>     AND COALESCE(p.value,d.value,@TrainingMode) NOT IN 
> ('TOE','TUM','NOTRAIN');
> COMMIT;
> 
I would reorder that where condition. I would first check for the preference 
value since on normal setups the preference table is way, way smaller then the 
token data table. And using that condition first will result in not executing 
the second condition (the one where we compute and compare against last_hit) at 
all if already the first condition does not return true.

> --
> -- Delete TUM tokens seen no more than 50 times
> --
> START TRANSACTION;
> DELETE LOW_PRIORITY QUICK
>   FROM t USING dspam_token_data t
>     LEFT JOIN dspam_preferences p ON p.preference = 'trainingMode' AND p.uid =
> t.uid
>     LEFT JOIN dspam_preferences d ON d.preference = 'trainingMode' AND d.uid 
> = 0
>   WHERE from_days(@tod...@purgeunused) > last_hit
>     AND COALESCE(p.value,d.value,@TrainingMode) = 'TUM'
>     AND innocent_hits + spam_hits < 50;
> COMMIT;
> 
And here I would reorder the conditions as well. First checking for the value 
of the preference and then for the last_hit and then for the "innocent_hits + 
spam_hits < 50". IMHO that should make the query slightly faster.

What do you think?

-- 
Kind Regards from Switzerland,

Stevan Bajić

------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
Dspam-devel mailing list
Dspam-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspam-devel

Reply via email to