Indexes in one-to-many relationship between two tables

Whenever you have a one-to-many relationship between two tables, you'll have a foreign key reference in the table on the many side of the relationship.

In most database systems, the primary key is automatically indexed; that's not always the case with the foreign key.

To ensure best performance on joins, make sure that every foreign key in the database is indexed. The next candidate for indexing is any column that will be used for sorting — any column that will consistently be used in an ―order by‖ clause of a SQL query.

You'll also want to index columns that will be used for restricting the returned data set, such as those that consistently appear in ―where clauses.