PK or simple key?

[Eamon Daly]

I don't know why I have so much trouble visualizing indexes,
but such is life. Imagine a simple table with only two
columns: user_id and foo_id. I will only ever be looking up
records in this table by user_id, but I will always retrieve
both fields. Figure a million rows and maybe 5 or so rows
per user_id.

Should the table be defined with a PK across all columns:

CREATE TABLE `user_foos` (
  `user_id` int(10) unsigned NOT NULL default '0',
  `foo_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`user_id`,`foo_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

or should the table have no PK and a key on user_id:

CREATE TABLE `user_foos` (
  `user_id` int(10) unsigned NOT NULL default '0',
  `foo_id` int(10) unsigned NOT NULL default '0',
  KEY `user_id_idx` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Or something else entirely? I'd guess the latter is the
Right Way, since the index will be significantly smaller,
but then I still need to go to the table to retrieve foo_id.

____________________________________________________________
Eamon Daly