how would an index on the base tables help in accessing rows in the intersection table.
typically, you query something like (using students and courses and an intersection of student_to_courses)
select *
from students s, students_to_courses sc, courses c
where s.student_name = 'TKYTE'
and s.student_id = sc.student_id
and sc.course_id = c.course_id;
Now, we would hope for an index on student_name - to find the row(s) in S fast.
We now have a set of student_ids for TKYTE.
We need to now access students_to_course to find the set of course_ids, we do that by student_id
Hence, we need an index on student_id in the SC table. We probably have that since student_id,course_id is the primary key - we have the primary key index.
However, we also frequently query the other direction:
select *
from students s, students_to_courses sc, courses c
where c.course_name = 'Intro to DBA'
and s.student_id = sc.student_id
and sc.course_id = c.course_id;
Here we start at course, goto SC by course_id - we need an index on course_id in SC therefore. We have an index on (student_id,course_id), but nothing that starts on course_id - so, we'd need to index that column - yes - in general