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

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