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