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

[Daevid Vincent]

Ah ha!

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`)
Or even 
  KEY  (`rep_password`, `rep_login`)

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?

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?

> -----Original Message-----
> From: Bruce Feist [mailto:bfeist@stripped] 
> >  
> >
> Here's a close analogy for you.  In a library, fiction books are 
> typically sorted first by author's last name, and then by 
> author's first 
> name.  Think "KEY (author_last, author_first).  This makes it fast to 
> find all books by an author with a given last name, and even 
> faster to 
> find all books given the author's first and last names... but 
> it doesn't 
> help if you need to find books by author's first name.
> 
> Bruce Feist
> 

And Dan wrote: 

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped] 
> 
> 
> Mysql will only use one index for a particular table, so a statement
> like SELECT * FROM mytable WHERE a_id=123 AND b_id=345 will be able to
> use the compound index to filter to exactly the records you're looking
> for.  With two separate indices, it'll use the index with the lowest
> cardinality, pull all the matching records, and discard the ones where
> the other field doesn't match your criteria.
> 
> The field order only matters if you also want to be able to do a query
> on a_id.  Mysql will be able to use a KEY (a_id,b_id), but not a
> (b_id,a_id) one, since the field it's interested in is not the first
> one.  most of the time, you'll end up generating two indexes:
> (a_id,b_id), and (b_id).