Re: Bayes innodb problems

On Wed, 26 Sep 2007 17:54:05 -0700, John D. Hardin wrote:

> On Wed, 26 Sep 2007, Micah Anderson wrote:
>

>> SELECT count(*)
>>                FROM bayes_token
>>               WHERE id = '4'
>>                 AND ('1190846660' - atime) > '345600';

That comes from /usr/share/perl5/Mail/SpamAssassin/BayesStore/SQL.pl line 243. It seems to calculate the expire delta, but in a way that can't use an index.

Maybe that query should be changed from:

AND (? - atime) > ?"

to:

AND atime < ? + ?"

does that make sense? The ? gets replaced by variables later (its a prepared statement), namely $newest_atime and $start * $i, so this change would make it so it can utilize the index, thus examining less rows (which could be locked) because mysql can't look up $newest_atime - atime from the index because that value is made up, so it uses the "id" portion of the index on (id, atime)... so if it doesn't have to calculate the value for $newest_atime + $something for every row and only needs to check the index then it would be much faster.

Do you need help?
In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related spamassassin.apache.org Links users Request more information about Pantek

Micah

ps - nils from #mysql is really the genius behind this information, not me Received on Wed Sep 26 23:31:36 2007