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]

Reply via email to