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]
