Got it. Thanks to all who replied: speed is the primary
concern here, so I'll be going with the first approach,
especially since I could use the unique constraint.
____________________________________________________________
Eamon Daly
----- Original Message -----
From: "Martijn Tonies" <m.tonies@stripped>
To: "Eamon Daly" <edaly@stripped>; <mysql@stripped>
Sent: Friday, November 11, 2005 2:41 AM
Subject: Re: PK or simple key?
>I addition to Glebs answer...
>
>
>> 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.
>
> The "right way" is to understand that indices and constraints
> (PK = primary key constraint) are conceptually different.
>
> A constraint is a logical thing, while an index is a physical
> and implementation thing. Most of the time, if not always,
> creating a primary/unique/foreign key constraint in a DBMS
> creates an underlying index for performance reasons, but
> it's really up to the DBMS to figure out how to do it.
>
> A constraint adds more functionality than an index. In general,
> "foreign key constraints" "point" to a PK or Unique Constraint.
> The PK or Unique Constraint (also knows as "candidate keys")
> allow you to uniquely target a row in a table AND tells the
> DBMS to make sure there are no duplicates.
>
> In addition to a PK, you are free to define indices on parts
> of the PK for performance reasons.
>
> Hope this helps,
>
> With regards,
>
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
