On Sat, 19 Dec 2009 14:30:28 +0100 (CET)
"Nicolas Grekas" <[email protected]> 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
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspam-devel
