Category Trees in MySQL: Adjacency List vs Closure Table for a Classifieds Site

#php #mysql #webdev #database

Classifieds sites live and die by their categories: "For Sale > Electronics > Phones" needs to load fast, show counts, and support breadcrumbs. I've used three approaches over the years. Here's where each one fits.

Adjacency list: simple, but recursive

CREATE TABLE categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id INT UNSIGNED NULL,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL,
  KEY (parent_id)
);

Easy to insert and move. On MySQL 8 you can fetch a subtree with a recursive CTE:

WITH RECURSIVE sub AS (
  SELECT id FROM categories WHERE id = ?
  UNION ALL
  SELECT c.id FROM categories c JOIN sub ON c.parent_id = sub.id
)
SELECT id FROM sub;

On MySQL 5.7 or older you're stuck with loops in PHP or a fixed number of self-joins.

Closure table: fast reads, a bit more bookkeeping

Store every ancestor/descendant pair:

CREATE TABLE category_paths (
  ancestor INT UNSIGNED NOT NULL,
  descendant INT UNSIGNED NOT NULL,
  depth TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (ancestor, descendant),
  KEY (descendant)
);

All listings under "Electronics", including subcategories, become one indexed join:

SELECT l.*
FROM listings l
JOIN category_paths p ON p.descendant = l.category_id
WHERE p.ancestor = ? AND l.status = 'active'
ORDER BY l.created_at DESC
LIMIT 30;

Breadcrumbs are just as easy: select ancestors of the current category ordered by depth descending.

Inserting a new category means copying the parent's ancestor rows:

INSERT INTO category_paths (ancestor, descendant, depth)
SELECT ancestor, :new_id, depth + 1 FROM category_paths WHERE descendant = :parent_id
UNION ALL SELECT :new_id, :new_id, 0;

Nested sets: skip it for classifieds

Nested sets are fast to read but painful to write, and admins reorganize categories more often than you'd think. I wouldn't use them here.

Counts without counting

Don't COUNT(*) on every page view. Keep a listing_count column per category, updated by a nightly job or on listing status changes, and roll it up through the closure table.

My pick

Adjacency list for the source of truth, a closure table for reads, both maintained in the same transaction. Category pages stay at one indexed query, and moving a branch is still manageable.

These patterns power the category pages across our network: Classifieds24.net, Classifieds24.info, Classifieds24.org, GlobalClassifieds.info, GlobalClassifieds.org, HirePros.pro, Realtor.pm and LinkMarket.app.

DEV Community