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