On Tue, 21 Nov 2006 13:42:09 +0100 Jonas Eckerman <[EMAIL PROTECTED]> wrote:
> > CREATE TABLE bayes_token ( > > PRIMARY KEY (id, token), > > INDEX bayes_token_idx1 (token), > > INDEX bayes_token_idx2 (id, atime) > > ) TYPE=MyISAM; > > > PRIMARY for `id` and `token` should not have INDEX for `id` and > > `token` added, too. > > Why not? > > IIRC the three indexes above makes perfect sense. Like this: > > "WHERE id=xxx AND token=xxx" will use the primary index. > > "WHERE token=xxx" will use the bayes_token_idx1 index. > > "WHERE id=xxx AND atime=xxx" will use the bayes_token_idx2 index. > > Again IIRC, the clause "WHERE token=xxx" should be faster with the > existance of the "bayes_token_idx1" index than without it. If the primary key was changed to (token, id), it should be able to be used in the second sort of query as well as the first, no? Or is MySQL not smart enough to recognize that it's got an index it could match on a prefix basis? > Or is it simply that the MySQL bayse store module never queries with > "token" as the first column in a WHERE clause? The position of a column in the WHERE clause shouldn't make a difference whether an index is used; the nature of SQL is such that WHERE clauses should be reorderable. I'm a PostgreSQL guy myself, but I would still be surprised if MySQL were limited in this way. Mike.
