SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Pasadena, California
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Mysql Multiple Primary Keys + Indexing?

    I am currently figuring out which tables have what in them for an application I'm writing as an excercise, and I have a bunch of tables that look like this:

    PHP Code:
    $query 'create table assignedgroups (
                userid int unsigned not null,
                groupid int unsigned not null,
                primary key (userid, groupid)
                )'
    ;
    $result mysql_query$query ); 
    My question is this: If I'm declaring the primary key to be (userid, groupid), does that automatically mean that both my userid column and my groupid column will be indexed? If not, what should I add to the columns to have them indexed? I searched through the Mysql manual, but I must have missed it. Thanks for your time

    -ToshiroOC

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    declaring (userid,groupid) as the primary key will create a compund index

    if you wanted to retrieve the groupids for a given userid=foo, the index would be used

    but if you wanted the userids for a given groupid=bar, the index might not be used, since you'd have to get every index entry

    you might want to add an index on groupid, or perhaps on (groupid,userid)

    another consideration is whether there are any other columns in the table besides those two

    rudy
    http://r937.com/

  3. #3
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Pasadena, California
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll go with key(groupid, userid) and see if that works. Thanks for your help!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •