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: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
