How do you keep a JOIN table performant?

I'm drawing up plans for a few new features on my site, and one could be "solved" using a join table.

Example schema:

Person table

• PK PersonId
• Name
• Age ...

PersonCheckin table

• PK FK PersonId
• PK FK CheckinId
• Date ...

Checkin table

• PK CheckinId
• CheckedInto ...

A join would be run to get the check in data for a person (connected by the PersonCheckin table). Since every person could check in an unlimited number of times, the PersonCheckin table could become very large.

I'd imagine this would cause some performance 开发者_StackOverflow社区issues. What are typical ways this is handled to keep performance high?

A join is considering the best performing means of connecting related tables.
But it really depends on the query, because it might not need to be a JOIN -- JOINing can inflate the record set on the parent tables side if there are more than one child record related, which means there could be a need for either GROUP BY or DISTINCT. EXISTS or IN is a better choice in such situations...

Indexes can help on the column(s) used in the JOIN criteria, on both sides of the relationship. In this example both sides are primary keys, which typically have the best index automatically created when the primary key is defined...

If you are going to execute this query very often and you want to achieve better performance just create a view on the database where you write the join query