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:[EMAIL PROTECTED] > > > > > 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:[EMAIL PROTECTED] > > > 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
