----- Original Message ----- From: "Daevid Vincent" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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:[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 > --------------------------------------------------------------------- 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
