-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Matt Kettler wrote:
> A quick diff of the 3.2 and 3.3 versions of these files shows this table > was changed: > > > 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 bayes_token_idx1 (token), <- deleted > INDEX bayes_token_idx2 (id, atime) <- renamed idx1 > ) TYPE=MyISAM; > > > > So token was both a primary key, and an index, which is redundant. How is that redundant? If you search for only a token, it would not be indexed, and would perform very poorly. In section 7.4.4 of the mysql docs: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3). MySQL cannot use an index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here: SELECT * FROM tbl_name WHERE col1=val1; SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; SELECT * FROM tbl_name WHERE col2=val2; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3). - -- David Morton <morto...@dgrmm.net> Morton Software & Design http://www.dgrmm.net - Ruby on Rails PHP Applications Maia Mailguard http://www.maiamailguard.com - Spam management for mail servers -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iD8DBQFLdtRDUy30ODPkzl0RAhuUAKDLlkErP+nXtPQ6gfHQwOQpBw5e7wCgvn+n 1VsGlPGW6GW9GoJrwE3cTgw= =PJew -----END PGP SIGNATURE-----
