A social network is a graph: each person is a vertex, and each relationship is an edge. Finding friends of friends means traversing that graph by exactly two edges. MySQL 8 can express both fixed-depth recommendations and bounded multi-hop paths directly, without maintaining a separate table or view for every level.
Decide What an Edge Means
A friendship is normally undirected: if Alice is Bob's friend, Bob is Alice's friend. Store that fact once, with the smaller identifier first. The ordering gives every pair one canonical representation:
CREATE TABLE person (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
display_name VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE friendship (
person_id_a BIGINT UNSIGNED NOT NULL,
person_id_b BIGINT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (person_id_a, person_id_b),
KEY idx_friendship_b_a (person_id_b, person_id_a),
CONSTRAINT chk_friendship_order
CHECK (person_id_a < person_id_b),
CONSTRAINT fk_friendship_a
FOREIGN KEY (person_id_a) REFERENCES person (id),
CONSTRAINT fk_friendship_b
FOREIGN KEY (person_id_b) REFERENCES person (id)
); The primary key prevents duplicate friendships. The check rejects self-links and reversed pairs, while the second index supports lookups from either endpoint. Normalize the identifiers when inserting:
INSERT INTO friendship (person_id_a, person_id_b)
VALUES (LEAST(?, ?), GREATEST(?, ?)); The four placeholders represent the same two identifiers twice. Application code can calculate the ordered pair before executing the statement instead. If the relationship means “follows” rather than “is friends with,” keep it directed and store (follower_id, followed_id) without mirroring it.
Expand Each Friendship into Two Directed Edges
Traversal is easiest when every edge has a source and a destination. A common table expression can expose both directions without storing redundant rows:
WITH edges AS (
SELECT person_id_a AS person_id,
person_id_b AS friend_id
FROM friendship
UNION ALL
SELECT person_id_b AS person_id,
person_id_a AS friend_id
FROM friendship
)
SELECT p.id, p.display_name
FROM edges AS e
JOIN person AS p ON p.id = e.friend_id
WHERE e.person_id = 1
ORDER BY p.display_name; UNION ALL is intentional. The canonical primary key prevents duplicate stored pairs, and the two branches represent opposite directions rather than competing copies of the same row.
Find Friends of Friends
A two-hop query first finds the direct friends, then expands their outgoing edges. Recommendations should normally exclude the starting person and anyone who is already a direct friend:
SET @person_id = 1;
WITH
edges AS (
SELECT person_id_a AS person_id,
person_id_b AS friend_id
FROM friendship
UNION ALL
SELECT person_id_b AS person_id,
person_id_a AS friend_id
FROM friendship
),
direct_friends AS (
SELECT friend_id
FROM edges
WHERE person_id = @person_id
),
second_degree AS (
SELECT e.friend_id
FROM direct_friends AS d
JOIN edges AS e ON e.person_id = d.friend_id
)
SELECT DISTINCT p.id, p.display_name
FROM second_degree AS candidate
JOIN person AS p ON p.id = candidate.friend_id
WHERE candidate.friend_id <> @person_id
AND NOT EXISTS (
SELECT 1
FROM direct_friends AS direct
WHERE direct.friend_id = candidate.friend_id
)
ORDER BY p.display_name; DISTINCT matters because the same person may be reachable through several mutual friends. Removing duplicates at the end preserves those independent paths during traversal. If the number of mutual friends is useful for ranking, replace the final SELECT DISTINCT in the preceding statement with this grouped result:
SELECT
p.id,
p.display_name,
COUNT(*) AS mutual_friends
FROM second_degree AS candidate
JOIN person AS p ON p.id = candidate.friend_id
WHERE candidate.friend_id <> @person_id
AND NOT EXISTS (
SELECT 1
FROM direct_friends AS direct
WHERE direct.friend_id = candidate.friend_id
)
GROUP BY p.id, p.display_name
ORDER BY mutual_friends DESC, p.display_name; A Reproducible Example
The following graph gives Alice two direct friends. Diego is connected through both of them, while Erin and Fatima are each connected through one:
INSERT INTO person (id, display_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Cara'),
(4, 'Diego'),
(5, 'Erin'),
(6, 'Fatima');
INSERT INTO friendship (person_id_a, person_id_b) VALUES
(1, 2),
(1, 3),
(2, 4),
(2, 5),
(3, 4),
(3, 6),
(4, 5); For @person_id = 1, the recommendation query returns Diego, Erin, and Fatima. The grouped version reports two mutual friends for Diego and one each for Erin and Fatima. Alice, Bob, and Cara are excluded.
Traverse More Than Two Levels
A recursive CTE can explore paths up to a chosen depth. The path is stored as a JSON array so a vertex already visited on the current path cannot be visited again:
SET @person_id = 1;
SET @max_depth = 5;
WITH RECURSIVE
edges AS (
SELECT person_id_a AS person_id,
person_id_b AS friend_id
FROM friendship
UNION ALL
SELECT person_id_b AS person_id,
person_id_a AS friend_id
FROM friendship
),
paths (origin_id, person_id, visited, depth) AS (
SELECT
@person_id,
e.friend_id,
JSON_ARRAY(@person_id, e.friend_id),
1
FROM edges AS e
WHERE e.person_id = @person_id
UNION ALL
SELECT
p.origin_id,
e.friend_id,
JSON_ARRAY_APPEND(p.visited, '$', e.friend_id),
p.depth + 1
FROM paths AS p
JOIN edges AS e ON e.person_id = p.person_id
WHERE p.depth < @max_depth
AND JSON_CONTAINS(
p.visited,
JSON_ARRAY(e.friend_id)
) = 0
)
SELECT
p.person_id,
person.display_name,
MIN(p.depth) AS degree_of_separation
FROM paths AS p
JOIN person ON person.id = p.person_id
GROUP BY p.person_id, person.display_name
ORDER BY degree_of_separation, person.display_name; MIN(depth) returns the shortest discovered distance within the configured limit. Cycle prevention is path-local: two different paths may still reach the same person, which is necessary when searching for a shortest route. The final grouping consolidates those alternatives.
A fixed limit such as five is an application decision, not proof that every pair of people is connected within that distance. Recursive traversal can grow rapidly in a dense graph, roughly with the average degree raised to the search depth. Keep the bound small and apply domain-specific filters as early as possible.
Why Fixed Views and Mirrored Tables Age Poorly
A separate mirrored edge table maintained by only an insert trigger duplicates the source of truth. Deleting or updating a friendship requires matching trigger logic, bulk imports must honor the same invariants, and failures become difficult to diagnose. One canonical row plus two indexes keeps the invariant in the schema.
Views named for level one, level two, level three, and so on also repeat almost identical joins and impose a depth limit in database objects. A two-hop CTE is clearer for recommendations, while a recursive CTE makes the depth an input to one query.
For very large or high-degree networks, neither arbitrary recursive SQL nor rebuilding every path on demand is free. Cache frequently requested two-hop recommendations, maintain derived results asynchronously when needed, or use a graph-oriented system when deep traversals are a primary workload. The relational model remains a good fit when relationships, constraints, and shallow neighborhood queries dominate.