List:General Discussion« Previous MessageNext Message »
From:Jeff Kilbride Date:March 19 2003 2:17am
Subject:Re: Please tell me why to use KEY (a_id, b_id) rather than KEY (a_id), KEY (b_id)
View as plain text  
----- Original Message -----
From: "Daevid Vincent" <daevid@stripped>
To: <mysql@stripped>
Sent: Tuesday, March 18, 2003 4:59 PM
Subject: RE: Please tell me why to use KEY (a_id, b_id) rather than KEY
(a_id), KEY (b_id)


> 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?

Exactly. There's  a bit of an explanation it the manual here:

http://www.mysql.com/doc/en/Multiple-column_indexes.html

I try to think of it this way: if I have a query that *always* uses more
than one column to search for data, it's a great candidate for a multiple
column index. The user/pass query you have above is a good example. I tend
to use multi-column indexes in reporting queries a lot. In sales reports,
for example, I'm almost always querying by date as well as some other
criteria like sales rep id. Having a multi-column index on (sales_rep_id,
date) would help when searching by sales_rep_id alone, or sales_rep_id and a
date range -- but wouldn't help when searching by date alone. In contrast,
having an index on (date, sales_rep_id) would help searching by date alone,
or date and sales_rep_id -- but not on sales_rep_id alone. The order of the
columns in the index really depends on your particular queries. Another
thing to keep in mind is that if all the columns in your query exist in the
index, then mysql can use *just* the index to return the data -- which means
it never even has to open the table. That can be a great optimization for
big tables with lots of columns.

> 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?
>

No, the mysql query optimizer is smart enough to figure this out. To see it
working, you can always use the EXPLAIN keyword:

http://www.mysql.com/doc/en/EXPLAIN.html

EXPLAIN will show you which index is being used for your SELECT statement
(or if no index is being used...). Invaluable when trying to optimize your
SELECTs.

--jeff

> > -----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).
>
>
> ---------------------------------------------------------------------
> 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 <mysql-thread134996@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-jeff=kilbride.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

Thread
Please tell me why to use KEY (a_id, b_id) rather than KEY (a_id), KEY (b_id)Daevid Vincent19 Mar
  • Re: Please tell me why to use KEY (a_id, b_id) rather than KEY (a_id), KEY (b_id)Dan Nelson19 Mar
RE: Please tell me why to use KEY (a_id, b_id) rather than KEY (a_id), KEY (b_id)Daevid Vincent19 Mar
  • Re: Please tell me why to use KEY (a_id, b_id) rather than KEY (a_id), KEY (b_id)Jeff Kilbride19 Mar
  • Re: Please tell me why to use KEY (a_id, b_id) rather than KEY (a_id),KEY (b_id)Bruce Feist19 Mar