From: Daevid Vincent
Date: March 19 2003 12:59am
Subject: RE: Please tell me why to use KEY (a_id, b_id) rather than KEY (a_id), KEY (b_id)
List-Archive: http://lists.mysql.com/mysql/134996
Message-Id: <005501c2edb2$d46b5240$dc0aa8c0@Locutus>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

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=3DInnoDB;

I would additionally add a=20
  KEY  (`rep_login`,`rep_password`)

Instead of=20
  KEY `rep_login` (`rep_login`),
  KEY `rep_password` (`rep_password`)
Or even=20
  KEY  (`rep_password`, `rep_login`)

Given that the query would most likely be something like:
SELECT * FROM rep_table WHERE rep_login =3D '$user' AND rep_password =3D
'$pass';

And I would never really search for just the password, so the=20
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 =3D '$pass' AND rep_login =3D
'$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]=20
> > =20
> >
> Here's a close analogy for you.  In a library, fiction books are=20
> typically sorted first by author's last name, and then by=20
> author's first=20
> name.  Think "KEY (author_last, author_first).  This makes it fast to=20
> find all books by an author with a given last name, and even=20
> faster to=20
> find all books given the author's first and last names... but=20
> it doesn't=20
> help if you need to find books by author's first name.
>=20
> Bruce Feist
>=20

And Dan wrote:=20

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped]=20
>=20
>=20
> Mysql will only use one index for a particular table, so a statement
> like SELECT * FROM mytable WHERE a_id=3D123 AND b_id=3D345 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.
>=20
> 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).