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.