Hey Dan, Thanks; I was really trying to ask about the potential performance gain, however. I don't care so much about the UNIQUEness, but the INDEXness.
See, I am wondering if I create an 2-column index wiht fk1 as the first component, will that index help me if I am refering fk2 in my query? > -----Original Message----- > From: Dan Nelson [mailto:[EMAIL PROTECTED] > Sent: Monday, April 24, 2006 1:40 PM > To: Fan, Wellington > Cc: mysql@lists.mysql.com > Subject: Re: Table design; 2-column index > > > In the last episode (Apr 24), Fan, Wellington said: > > If I have a table: > > > > CREATE TABLE t ( > > id int(11) NOT NULL auto_increment, > > fk1 mediumint(9) NOT NULL default '0', > > fk2 smallint(6) NOT NULL default '0', > > PRIMARY KEY (id), > > UNIQUE KEY idxfk1 (fk1,fk2), > > UNIQUE KEY idxfk2 (fk2,fk1) > > ) TYPE=MyISAM; > > > > I will about half the time have a query "WHERE fk1 IN (....)" and > > about the other half the time have "WHERE fk2 IN (....)" > > > > Does it make sense to define the UNIQUE KEYS the way I have? > > You only need one unique index to enforce uniqueness, so you > can safely > convert your idxfk2 to a single-column regular index and save a little > bit of space. > > ALTER TABLE t drop key idxfk2, add key idxfk2 (fk2); > > -- > Dan Nelson > [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
