Fields Other than the PK Need to be Indexed

The Table Primary Key (PK) is automatically an index.

Indexes should be used whenever a relationship needs to be established between two tables using a field other than the PK E.g. both fields included in the ON table1.field1=table2.field3 clause. Making both fields indexes allows MySQL to JOIN the two tables much more efficiently and much faster.

The working of an index is hidden from the user but involves MySQL setting up lookup pointers.