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



---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php