MySQL indexing

[kenelder]

In an on-line learning database, I have an enrollments table where each record is uniquely identified by its courseID and studentID. Therefore the primary key consists of those two fields.

But in the login process, I need to find enrollment records that match the email address and password entered on a login page. (It is possible that more than one record exists with the same email/password.) It would be inefficient to search sequentially through all the enrollments looking for those with a specific email/password. What is an efficient way of quickly finding those records?

[r937]

the email and password columns must be in the student table, not the enrollment

the enrollment table will have, as you described, courseID and studentID

you said "It is possible that more than one record exists with the same email/password" but i'll bet you were thinking of the enrollment table, where the same student will have multiple enrollments

by the way, with courseID and studentID together as the primary key, the same student can enroll for the same course only once, so if you need to, add a third column to the primary key for repeat enrollments


as for your question about looking up the email and password, in your student table, assign a unique key to them

in the login procedure, retrieve email and password from the student table to authorize the visitor, but then carry the studentID as a session variable and use it on the page which looks up the student's courses by doing a query on the enrollments table using the session studentID as the value:

select coursename
  from enrollments
inner
  join courses
    on nerollments.courseID = courses.courseID
 where studentID = sessionID