I just found out that bayes_token table in mailfiltering guide codelisting 10.2 is not very suitably indexed. --- CREATE TABLE bayes_token ( id int(11) NOT NULL default '0', token char(5) NOT NULL default '', spam_count int(11) NOT NULL default '0', ham_count int(11) NOT NULL default '0', atime int(11) NOT NULL default '0', PRIMARY KEY (id, token) ) TYPE=MyISAM; --- In my case SpamAssassin is every now and then performing queries against atime column. That is not indexed at all in the mailfiltering guide. Slow queries logged includes SELECTS like this one: --- SELECT min(atime) FROM bayes_token WHERE id = '1'; --- My bayes_token table contains about 8,6 million records, so a full table scan takes a while. Right after I created a new index with --- CREATE INDEX id_atime_index ON bayes_token(id, atime); --- MySQL started to perform A LOT faster. Check this out: --- mysql> SELECT min(atime) FROM bayes_token WHERE id = '1'; +------------+ | min(atime) | +------------+ | 1176345812 | +------------+ 1 row in set (0.00 sec) mysql> SELECT min(atime) FROM bayes_token IGNORE INDEX (id_atime_index) WHERE id = '1'; +------------+ | min(atime) | +------------+ | 1176345812 | +------------+ 1 row in set (19.96 sec) --- My suggestion to this problem is to initially create table with this command: --- CREATE TABLE bayes_token ( id int(11) NOT NULL default '0', token char(5) NOT NULL default '', spam_count int(11) NOT NULL default '0', ham_count int(11) NOT NULL default '0', atime int(11) NOT NULL default '0', PRIMARY KEY (id, token), INDEX (id, atime) ) TYPE=MyISAM; --- Reproducible: Always
Preliminary feedback on #gentoo-dev seems to agree that adding an index is helpful.
Thanks for your suggestion. Fixed in CVS.
Thank you very much!