List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 19 2003 12:04am
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  
In the last episode (Mar 18), Daevid Vincent said:
> I wouldn't say I was a newbie at all. I've been coding in PHP, mySQL
> for like 6 years now. But I just never learned (or maybe had a reason
> to) why I would use "KEY (a_id, b_id)" rather than "KEY (a_id)" and
> "KEY (b_id)". Would someone be so kind as to point me at an online
> tutorial or just explain what the difference is or what the use of
> "KEY (a_id, b_id)", and does it matter if I reverse them like "KEY
> (b_id, a_id)"? I mean, I understand that the key will be a hybrid of
> the two columns, but why would you want that? A friend tried to
> explain it in the case you have a third table that is what I call a
> "glue" table, but I still don't see how this works.

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).

-- 
	Dan Nelson
	sql,query
	dnelson@stripped
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