Managing Hierarchical Data in MySQL: Don’t Let ‘Category Trees’ Crash Your Server

When category trees ‘devour’ system resources

It was 2 AM when my phone started alerting me non-stop. The monitoring dashboard showed the Database CPU spiking to 100% and staying there. After tracing the issue, I discovered a product category query that was running “infinitely.”

At the time, the system was using the Adjacency List model (a simple parent-child relationship). When the category tree hit 15 levels with over 20,000 records, the recursive queries (Recursive CTE) began to exhaust the server’s resources. This was a costly lesson in choosing the wrong data structure from the start.

Here are three common techniques for managing tree-like data in MySQL that I’ve refined after several real-world incidents.

1. Adjacency List: Simple but easily overwhelmed

This is the most instinctive approach for every developer. You simply add a parent_id column that points to the ID of the parent record.

Table Structure

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INT DEFAULT NULL,
    INDEX (parent_id),
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

Practical Evaluation

• Pros: Adding or moving a branch is extremely fast. You only need to update a single parent_id value.
• Cons: Querying the entire tree is very expensive. With MySQL versions below 8.0, you have to use application code for recursion. From MySQL 8.0 onwards, even with CTE support, performance drops significantly as the depth of the tree increases.

Example Query with CTE (MySQL 8.0+)

WITH RECURSIVE category_path (id, name, path) AS (
    SELECT id, name, name as path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name)
    FROM category_path cp JOIN categories c
    ON cp.id = c.parent_id
)
SELECT * FROM category_path;

2. Nested Set Model: Optimized for reading, a ‘nightmare’ for writing

This model eliminates parent_id. Instead, it uses two values, lft (left) and rgt (right), to wrap child nodes. Imagine each node as a box, where child nodes are smaller boxes nested inside larger ones.

How it works

To retrieve all descendants of a node, you don’t need recursion. A simple BETWEEN statement will do:

SELECT * FROM nested_categories 
WHERE lft BETWEEN 10 AND 25 
ORDER BY lft ASC;

Pros and Cons

• Pros: Incredibly fast read speeds. It is perfectly suited for news sites or product categories that rarely change.
• Cons: Write operations are painful. When you insert a node in the middle, MySQL must update the lft and rgt values for half the table. For a table with 100,000 rows, a single insert can lock the entire table for several seconds.

3. Closure Table: A balanced and modern solution

This is my preferred technique for large projects. Instead of storing relationships in the main table, we separate them into an auxiliary table to store every path between nodes.

Table Structure

CREATE TABLE category_hierarchy (
    ancestor INT NOT NULL, -- Ancestor ID
    descendant INT NOT NULL, -- Descendant ID
    path_length INT NOT NULL, -- Depth
    PRIMARY KEY (ancestor, descendant)
);

Why use a Closure Table?

It solves both problems: fast reading and reasonably fast writing. To find all descendants of node 1, you just need to join the auxiliary table. Moving an entire branch only takes a few simple DELETE and INSERT statements on the relationship table.

• Pros: Most flexible, supports multiple parents for a single node (multi-hierarchy).
• Cons: Consumes more disk space. For a tree 10 levels deep, each new record can generate up to 11 additional rows in the relationship table.

Performance Comparison Table

Real-world experience for you

Don’t try to find the perfect model; find the most suitable one. If you’re building a simple Todo app, use an Adjacency List. If you’re building an E-commerce system with millions of daily views, the Closure Table is the safest bet for a good night’s sleep.

In that 2 AM incident years ago, I had to use Redis to temporarily cache the entire category tree to save the server. Afterward, the team spent three days fully migrating to a Closure Table. The results were surprising: CPU load dropped from 90% to under 10%, and most importantly, I was no longer woken up in the middle of the night.