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:dnelson@stripped]
> Sent: Monday, April 24, 2006 1:40 PM
> To: Fan, Wellington
> Cc: mysql@stripped
> 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
> dnelson@stripped
>
