List:General Discussion« Previous MessageNext Message »
From:Wellington Fan Date:April 24 2006 6:07pm
Subject:RE: Table design; 2-column index
View as plain text  
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
> 
Thread
Table design; 2-column indexWellington Fan24 Apr
  • Re: Table design; 2-column indexDan Nelson24 Apr
RE: Table design; 2-column indexWellington Fan24 Apr
Re: Table design; 2-column indexDan Nelson24 Apr