Does a MySQL Primary Key on two columns help with queries on the second column?

Question

I have two classes, Foo and Bar. Each Bar will contain many Foos. Foos can be in multiple Bars but each Foo can only be in a given Bar once. I have the following table structure:

CREATE TABLE `bar_foos` (
   `bar_id` INT UNSIGNED NOT NULL,
   `foo_id` INT UNSIGNED NOT NULL,
   PRIMARY KEY ( `bar_id` , `foo_id` )
);

This should work fine for my many-to-many relationship. My question is, if I want my code to be able to check to see if a Foo is in use by any Bars, I.E. to tell the user "This Foo cannot be deleted because it is in use by 5 Bars", does the PRIMARY KEY index help me with a query like

SELECT * FROM `bar_foos` WHERE `foo_id`=2

or

SELECT COUNT(*) FROM `bar_foos` WHERE `foo_id`=2

Or, do I need a separate index for the foo_id column alone?

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

"If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL cannot use an index if the columns do not form a leftmost prefix of the index."

Answer 2

Imagine if you were searching a phone book where there is an index on last names with a secondary index on the first name. (And this is realy how it is.)

• good - where last_name = 'Smith' and first_name = 'John'
• good - where last_name = 'Smith'
• bad - where first_name = 'John'

'John's can appear on any page in the phone book so it does not narrow it down.

So if you want to use index on first names you would have to 'print a new phone book that ordered names by first name. Or, in a database, create a new index with first names as the first column index.