Six joins deep and getting slower
"People connected to you through at most four introductions" is written as a query joining the connections table to itself four times. It works at small depth and the cost multiplies with every hop.
Each extra degree of separation adds another self-join over a table whose rows are relationships, and the planner has to materialise the intermediate sets.
Store the relationships in a shape whose cost follows the connections rather than the table.
Components — tap one, then tap a slot on the diagram
!Each extra degree of separation adds a self-join, and the query time multiplies with it.
Outside every boundary: connections (joined to itself 4×; FAILED: multiplying), Four hops out (who is connected), an empty slot for the how relationships are stored Connections: Four hops out calls how relationships are stored (step 1) how relationships are stored calls connections — walk, don't join (step 2)
connectionsjoined to itself 4×multiplying
Four hops outwho is connected