Re: Please tell me why to use KEY (a_id, b_id) rather than KEY (a_id), KEY (b_id)

[Bruce Feist]

Daevid Vincent wrote:

>So if I had:
>
>CREATE TABLE `rep_table` (
>  `rep_id` smallint(5) unsigned auto_increment,
>  `rep_login` varchar(15) NOT NULL default '',
>  `rep_password` varchar(15) NOT NULL default '',
>  `rep_fname` varchar(255) NOT NULL default '',
>  `rep_lname` varchar(255) NOT NULL default '',
>  PRIMARY KEY  (`rep_id`)
>) TYPE=InnoDB;
>
>I would additionally add a 
>  KEY  (`rep_login`,`rep_password`)
>
>Instead of 
>  KEY `rep_login` (`rep_login`),
>  KEY `rep_password` (`rep_password`)
>
Precisely!

>Or even 
>  KEY  (`rep_password`, `rep_login`)
>
This would actually be just as good if you were doing a lookup on both
fields.

>Given that the query would most likely be something like:
>SELECT * FROM rep_table WHERE rep_login = '$user' AND rep_password =
>'$pass';
>
>And I would never really search for just the password, so the 
>KEY `rep_password` (`rep_password`)
>Is sorta a useless index?
>
Yes.

>Furthermore, if I understand correctly, if I did the query like so:
>SELECT * FROM rep_table WHERE rep_password = '$pass' AND rep_login =
>'$user';
>I would NOT get the benefit of the index either since I changed the order of
>my search, is that true?
>
Almost certainly *not*.  Most RDBMSs have optimizers good enough to
realize that the order of the conditions is irrelevant.  I assume that
this is true of MySql.  But, I'm a newcomer to MySql, and I could be wrong.

Bruce Feist