CODEWITHSUNDEEP
sqllinq
Chaddeus
Chaddeus

Reputation: 13376

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

PersonCheckin table

Checkin table

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 issues. What are typical ways this is handled to keep performance high?

Upvotes: 1

Views: 88

Answers (2)

Lorenzo
Lorenzo

Reputation: 29427

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

Upvotes: -1

OMG Ponies
OMG Ponies

Reputation: 332781

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...

Upvotes: 2

Related Questions