List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 21 2005 1:17am
Subject:Re: Question about combination PRIMARY keys and INDEX
View as plain text  
At 9:10 +1000 4/21/05, Daniel Kasak wrote:
>Daevid Vincent wrote:
>
>>If I have a table with a primary key like this:
>>
>>CREATE TABLE `answers` (
>>`qid` INT UNSIGNED NOT NULL ,
>>`userid` INT UNSIGNED NOT NULL ,
>>`groupid` INT UNSIGNED NOT NULL ,
>>`comments` TEXT NOT NULL ,
>>  PRIMARY ( `qid` , `userid` , `groupid` )
>>);
>>
>>But I will also be searching in various pages, for all 'answers' from a
>>certain userid or say that belong to a certain groupid, do I still need to
>>create separate INDEXes for those fields, or is it enough to have them in
>>that combination PRIMARY key?
>> 
>>
>You need to have separate indexes. If you have a composite index and
>just have one field in the where clause ( or in a join ), the index
>won't be used at all. It says this somewhere in the documentation.

Not quite.  The index could be used if were searching for a column or
columns that form a leftmost prefix of the index.  That means it could
be used if you were searching for qid values, or qid+userid values.

But just groupid values are not leftmost prefixes of the index, so
you do need a separate index for groupid.

>
>Also, while the above table definition is legal, I would have an
>auto_increment primary key, and then if you want to make ( `qid` ,
>`userid` , `groupid` ) unique, define a unique index across them.
>
>--
>Daniel Kasak
>IT Developer
>NUS Consulting Group
>Level 5, 77 Pacific Highway
>North Sydney, NSW, Australia 2060
>T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
>email: dkasak@stripped
>website: http://www.nusconsulting.com.au
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Thread
Question about combination PRIMARY keys and INDEXDaevid Vincent21 Apr
Re: Question about combination PRIMARY keys and INDEXDaniel Kasak21 Apr
  • Re: Question about combination PRIMARY keys and INDEXPaul DuBois21 Apr