From: Dan Nelson
Date: April 24 2006 5:39pm
Subject: Re: Table design; 2-column index
List-Archive: http://lists.mysql.com/mysql/197280
Message-Id: <20060424173935.GF73063@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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