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
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
