List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:April 20 2005 11:10pm
Subject:Re: Question about combination PRIMARY keys and INDEX
View as plain text  
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.

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
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