5 messages in com.mysql.lists.mysqlRe: Please tell me why to use KEY (a_...
FromSent OnAttachments
Daevid Vincent18 Mar 2003 15:50 
Dan Nelson18 Mar 2003 16:04 
Daevid Vincent18 Mar 2003 16:59 
Jeff Kilbride18 Mar 2003 18:17 
Bruce Feist18 Mar 2003 18:57 
Subject:Re: Please tell me why to use KEY (a_id, b_id) rather than KEY (a_id), KEY (b_id)
From:Jeff Kilbride (je@kilbride.com)
Date:03/18/2003 06:17:12 PM
List:com.mysql.lists.mysql

----- Original Message ----- From: "Daevid Vincent" <dae@daevid.com> To: <mys@lists.mysql.com> 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:bfe@flock.org]

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:dnel@allantgroup.com]

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