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" <[EMAIL PROTECTED]>
To: "Eamon Daly" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
