List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 24 2006 5:39pm
Subject:Re: Table design; 2-column index
View as plain text  
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