Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007

    Unanswered: Indexing a Many to Many Table


    I have many to many table called Jobseeker_IndustryExp. It has the following fields:

    recordid - this auto increment number
    jobseekerid - this foriegn key from jobseeker table
    industryid this is foriegn key from industry table

    Would you recomend indexing the jobseekerid and industryid fields in the above table - a frequent Select query will be used to do an inner join on the above table to search for jobseekers who match certain industry experience?

  2. #2
    Join Date
    Mar 2007
    Why do you have an auto_increment field in there? Surely because this is a mapping table you don't need that field. And i'm guessing (if you've designed your DB correctly) that the jobseekerid and industryid combination ought to be unique.

    Personally I would do the following :
    1) Remove the auto_increment
    2) Create the primary key on (jobseekerid,industryid)

  3. #3
    Join Date
    Mar 2007
    Yep u right. both jobseekerid and industryid are unique primary keys of their respected tables. The recordid i just created - no particular reason! Whats the syntax for defining a compound primary key though?

  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by ozzii
    Whats the syntax for defining a compound primary key though?
    create table Jobseeker_IndustryExp
    ( jobseekerid integer not null 
    , industryid  integer not null 
    , primary key (jobseekerid, industryid)
    , constraint job_fk
         foreign key (jobseekerid) references jobseeker
    , constraint industry_fk
         foreign key (industryid) references industry 
    say, ozzii, would you mind answering my question in this other thread? | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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