Index join columns (parent_id). Add depth limits (WHERE level < 10). Use covering indexes. Typical hierarchies: org charts 5-7 levels, categories 3-5 levels. With index: 0.3s vs without: 45s on 1M rows.

Index parent_id column: 10-100x speedup

Add depth limit: prevents runaway queries

Use covering indexes: include all selected columns

Filter in anchor member when possible

Benchmark: 1M rows, 7 levels - no index: 45s, with index: 0.3s

Consider materialized path for read-heavy workloads

Limit result set with WHERE in final SELECT

Use EXPLAIN to verify index usage

Infinite recursion: missing or wrong termination condition

Cycle detection: graph data needs path tracking

Using UNION instead of UNION ALL (breaks recursion)

No index on join columns (massive performance hit)

Forgetting RECURSIVE keyword (syntax error)

Selecting too many columns (memory issues)

Not testing termination logic

Assuming tree structure when data has cycles