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