List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 11 2005 8:41am
Subject:Re: PK or simple key?
View as plain text  
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