Hi!
Am Fr, den 15.04.2005 schrieb Fan, Wellington um 18:50: > Hello all, > > I have a link table that relates 'Profiles' with 'Categories': > > CREATE TABLE lnk_profile_category ( > profile_fk int(11) NOT NULL default '0', > category_fk int(11) NOT NULL default '0', > PRIMARY KEY (category_fk,profile_fk) > ) TYPE=MyISAM; > > If I expect that my most frequent queries are going to be those that select > profiles in a given category -- i.e.: (select * from profile > <Yadda-Yadda-JOIN> where category.label = 'widgets' ) -- is it better to > define my PK as: > > 1. PRIMARY KEY (category_fk,profile_fk) > --Rather than-- > 2. PRIMARY KEY (profile_fk,category_fk) > > ?? Assuming that "category.label = 'widgets'" leads to an equality predicate on column "category_fk", sure the first order is better. For a coarse analogy: Try to look up somebody in a phone book whose family name "Smith-Miller" you do not know, only the given name "Jim". A multi-column primary key or index is comparable to a phone book whose entries are sorted by family name, then first name, then address: It can only be used if the leading column value/s is/are known, because these are the (most) significant ones. Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
