"Fan, Wellington" <[EMAIL PROTECTED]> wrote on 04/15/2005 12:50:07 PM:
> 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) > > ?? > If you have the room, I would do both. That way you are covered either way. If you have to choose, I would run an EXPLAIN on the same query, twice. Once with the index on (cat,pro) the other time on (pro,cat) and see if the engine produces a different query plan. However, since you use category table in the WHERE clause more often, I would (for no really good reason) use the (cat, pro) index. It's just my gut feeling. (I assume you already have an index on category for label?) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
