List:General Discussion« Previous MessageNext Message »
From:Eamon Daly Date:November 10 2005 10:33pm
Subject:PK or simple key?
View as plain text  
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

Thread
PK or simple key?Eamon Daly10 Nov
  • Re: PK or simple key?Gleb Paharenko11 Nov
  • Re: PK or simple key?Jigal van Hemert11 Nov
  • Re: PK or simple key?Martijn Tonies11 Nov
  • Re: PK or simple key?Eamon Daly11 Nov