Deep Thought

Exercise_SQL_vs_Graph

5 min read

Exercise: SQL vs. Graph

Belongs to: Graph_Databases · Duration: ~50 min · Work in pairs

The same social shop is loaded into MySQL and Neo4j. You answer the same questions in both systems and compare: How long is the query? What happens if the question changes slightly? How much work does each database do?

0. Setup (5 min)

In the nosql-lab folder (skip what is already running from last lesson):

docker compose --profile graph up -d
docker exec neo4j cypher-shell -u neo4j -p insy-neo4j -f /import/graph_lab.cypher
docker exec graph-mysql sh -c "mysql -uroot -pinsy < /import/graph_lab.sql"

The two load commands may fail during the first ~30 seconds while the databases start — just repeat them.

Open MySQL in a terminal:

docker exec -it graph-mysql mysql -uroot -pinsy graph_lab

and Neo4j in the browser: http://localhost:7474 (neo4j / insy-neo4j).

The MySQL tables:

person(id, name, city)
product(id, name, category, price)
friendship(person_id, friend_id)       -- stored in BOTH directions: (1,4) and (4,1)
purchase(person_id, product_id, bought_on)

Anna Berger has id = 1. Fill in this table while you work:

TaskResult MySQLResult Neo4jLines of SQLLines of CypherTime MySQLTime Neo4j
1
2
3
5

Time: MySQL prints it after each result (… rows in set (0.01 sec)). Neo4j shows it below the result (“Started streaming … after X ms”). Run each query twice and take the second time — the first run includes warming up caches.

1. Anna’s friends (5 min)

SQL:

SELECT p.name
FROM friendship f JOIN person p ON p.id = f.friend_id
WHERE f.person_id = 1
ORDER BY p.name;

Cypher: write it yourself (you did this last lesson).

2. Everyone within 2 steps (8 min)

“How many different people can Anna reach in one or two steps (not counting herself)?”

SQL:

SELECT COUNT(DISTINCT x) FROM (
    SELECT f1.friend_id AS x
    FROM friendship f1
    WHERE f1.person_id = 1
  UNION
    SELECT f2.friend_id
    FROM friendship f1
    JOIN friendship f2 ON f2.person_id = f1.friend_id
    WHERE f1.person_id = 1
) reach
WHERE x <> 1;

Cypher:

MATCH (a:Person {id: 1})-[:FRIENDS_WITH*1..2]-(x:Person)
WHERE x <> a
RETURN count(DISTINCT x)

Explain why the SQL query needs a UNION.

3. Everyone within 3 steps (8 min)

Change both queries from task 2 so they answer “within 3 steps”.

  • What did you have to change in Cypher?
  • What did you have to change in SQL? How long would the SQL query be for 6 steps?

4. Watch the rows explode (8 min)

A SQL self-JOIN does not know who it has already visited. Every path is built as a row — even if it leads to the same person again. Count those rows:

-- rows needed for exactly 4 steps
SELECT COUNT(*)
FROM friendship f1
JOIN friendship f2 ON f2.person_id = f1.friend_id
JOIN friendship f3 ON f3.person_id = f2.friend_id
JOIN friendship f4 ON f4.person_id = f3.friend_id
WHERE f1.person_id = 1;

Run it for 4, then add f5 and run it for 5, then add f6 for 6 steps. Write down the number of rows and the time. There are only 1 000 people in the database — what do you notice?

StepsRows builtTime
4
5
6

Now let Neo4j show you how much work it does. Put PROFILE in front of the query and look at the plan. The summary shows the total db hits (= how often Neo4j touched the store) — note that number:

PROFILE
MATCH (a:Person {id: 1})-[:FRIENDS_WITH*1..6]-(x:Person)
WHERE x <> a
RETURN count(DISTINCT x)

SQL has a better tool for this: a recursive CTE. It remembers what it has already found:

WITH RECURSIVE reach (id, depth) AS (
    SELECT 1, 0
  UNION
    SELECT f.friend_id, r.depth + 1
    FROM reach r JOIN friendship f ON f.person_id = r.id
    WHERE r.depth < 6
)
SELECT COUNT(DISTINCT id) FROM reach WHERE id <> 1;

Run it, too. Compare its length and readability with the Cypher query.

5. Recommendations (8 min)

“Which products did Anna’s friends buy that Anna does not have yet? Rank by the number of friends.”

SQL:

SELECT pr.name, COUNT(DISTINCT pu.person_id) AS friends
FROM friendship f
JOIN purchase pu ON pu.person_id = f.friend_id
JOIN product pr  ON pr.id = pu.product_id
WHERE f.person_id = 1
  AND pu.product_id NOT IN (SELECT product_id FROM purchase WHERE person_id = 1)
GROUP BY pr.id, pr.name
ORDER BY friends DESC, pr.name
LIMIT 5;

Cypher: you know it from last lesson (task 5).

Now the product manager changes the requirement: “Count friends and friends of friends.” Change both queries. Which one was easier to change?

6. Preview: how are two people connected? (5 min)

“Through which people is Anna connected to Ben Huber?”

Cypher:

MATCH p = shortestPath(
  (a:Person {name: "Anna Berger"})-[:FRIENDS_WITH*]-(b:Person {name: "Ben Huber"})
)
RETURN p

SQL (just run it — don’t try to write this yourself):

WITH RECURSIVE walk (id, depth, path) AS (
    SELECT 1, 0, CAST('1' AS CHAR(200))
  UNION ALL
    SELECT f.friend_id, w.depth + 1, CONCAT(w.path, ',', f.friend_id)
    FROM walk w JOIN friendship f ON f.person_id = w.id
    WHERE w.depth < 4 AND FIND_IN_SET(f.friend_id, w.path) = 0
)
SELECT depth, path FROM walk WHERE id = 2 ORDER BY depth LIMIT 1;

How many steps apart are Anna and Ben? Look up the names of the people on the path (SELECT id, name FROM person WHERE id IN (…);). What would you have to change in the SQL query if they were 7 steps apart? (Path algorithms are a topic of the next block.)

Clean up

docker compose --profile graph down

Hand-in

  1. The completed tables from the setup and task 4.
  2. Your queries for tasks 1, 3 and 5 (both versions).
  3. Short answers:
    • a) Why does the number of rows in task 4 grow so fast, although there are only 1 000 people?
    • b) Which is the bigger advantage of Neo4j in today’s tasks: speed or expressiveness? Justify with your measurements.
    • c) Name one question from today that you would still rather answer with SQL — or explain why there is none.
  4. Complete the classification grid: compare your graph column with the one in the scriptum.

Graph View

Backlinks