Generating 5 Million Hierarchical Rows in Under 100 Seconds Using Pure SQL

When evaluating database query performance, indexing strategies, or scale bottlenecks, realistic volumes of test data are essential. Testing queries against small datasets of a few hundred rows masks full-table scans, inefficient joins, and cache hit misconfigurations that only manifest when dealing with millions of records.

Traditionally, developers write custom external scripts in Python, Node.js, or Go to generate synthetic records. However, script-driven generation frequently suffers from:

1. Network Round-Trip Overhead: Sending individual or small-batch INSERT statements incurs massive network latency and socket overhead.
2. Memory & State Management: The application process must track auto-incremented primary keys in client-side memory to wire up foreign key dependencies (parent-child hierarchies).
3. Serialization Costs: Transforming objects into SQL statements and re-parsing them within the database engine drastically degrades throughput.

By keeping the data generation logic strictly within the database engine using Cartesian products (Cross Joins) and INSERT INTO ... SELECT pipelines, you can generate over 5 million relational records with foreign key hierarchies in roughly 83 seconds.

---

The Target Data Model: A Multi-Level Taxonomy

To simulate realistic relational data, we model a 3-tier taxonomy tree (similar to Udemy’s navigation structure: Categories $\rightarrow$ Subcategories $\rightarrow$ Topics).

graph TD
    A[Categories: 50 rows] -->|1 : 100| B[Subcategories: 5,000 rows]
    B -->|1 : 1000| C[Topics: 5,000,000 rows]

Volume Math

• Categories (Type 1): 50 records (Root level, parent_id = NULL)
• Subcategories (Type 2): 50 categories $\times$ 100 subcategories/category = 5,000 records
• Topics (Type 3): 5,000 subcategories $\times$ 1,000 topics/subcategory = 5,000,000 records
• Total Rows Generated: $50 + 5,000 + 5,000,000 = \mathbf{5,005,050\text{ rows}}$

Schema Definition

A single self-referencing table topics represents the entire taxonomy tree:

CREATE TABLE topics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(256) NOT NULL,
    parent_id INT DEFAULT NULL,
    type SMALLINT NOT NULL, -- 1: Category, 2: Subcategory, 3: Topic
    CONSTRAINT fk_topics_parent FOREIGN KEY (parent_id) REFERENCES topics(id),
    INDEX idx_topics_type (type)
);

---

The Core Mechanism: Cartesian Amplification

The fundamental secret to generating millions of records natively in SQL without procedural loops is the Cartesian Product (Cross Join).

When two sets $A$ and $B$ are joined without a WHERE or ON predicate, relational engines produce $A \times B$ combinations. By chaining small tables together, row counts amplify exponentially:

$\text{Total Rows} = |T_1| \times |T_2| \times \dots \times |T_k|$

To drive this amplification, we construct a compact seed lookup table called counters.

---

Step 1: Bootstrapping the Helper Tables

Instead of inserting 1,000 numbers manually to create our counter source, we can use Cartesian joins starting from just 10 digits ($0$ through $9$).

1.1 Create and Seed the digits Table

CREATE TABLE digits (
    id CHAR(1) PRIMARY KEY
);

INSERT INTO digits (id) VALUES 
('0'), ('1'), ('2'), ('3'), ('4'), 
('5'), ('6'), ('7'), ('8'), ('9');

1.2 Generate 1,000 Counter Rows via a 3-Way Cross Join

Joining digits three times ($10 \times 10 \times 10$) produces exactly 1,000 strings from '000' through '999':

CREATE TABLE counters (
    id CHAR(3) PRIMARY KEY
);

INSERT INTO counters (id)
SELECT CONCAT(d1.id, d2.id, d3.id)
FROM digits d1
CROSS JOIN digits d2
CROSS JOIN digits d3
ORDER BY 1;

This execution completes in a few milliseconds and gives an amplification generator with a maximum fan-out capability of 1,000.

---

Step 2: Populating Categories (Tier 1)

We need 50 top-level categories. Because categories have no parent, parent_id is NULL, and type is set to 1.

INSERT INTO topics (name, parent_id, type)
SELECT 
    CONCAT('cat-', c.id) AS name,
    NULL AS parent_id,
    1 AS type
FROM (
    SELECT id FROM counters LIMIT 50
) AS c;

• Rows generated: 50
• Execution time: ~130 ms

---

Step 3: Populating Subcategories (Tier 2)

For each of the 50 categories, we need 100 distinct subcategories.

We take all existing categories (type = 1), cross join them with 100 rows from counters, and project the category’s primary key directly into parent_id:

INSERT INTO topics (name, parent_id, type)
SELECT 
    CONCAT('subcat-', category_id, '-', counter_id) AS name,
    category_id AS parent_id,
    2 AS type
FROM (
    SELECT 
        cat.id AS category_id,
        cnt.id AS counter_id
    FROM (
        SELECT id FROM topics WHERE type = 1
    ) AS cat
    CROSS JOIN (
        SELECT id FROM counters LIMIT 100
    ) AS cnt
) AS subcat_matrix;

How It Works:

1. The inner derived table joins 50 categories against 100 counter rows ($50 \times 100 = 5,000$).
2. The outer query maps category_id into the parent_id column, maintaining strict foreign key integrity without needing client-side ID lookups.
3. Rows generated: 5,000
4. Execution time: ~560 ms

---

Step 4: Populating 5 Million Topics (Tier 3)

For each of the 5,000 subcategories, we need 1,000 topics. We cross join all subcategories (type = 2) against the full 1,000 rows of the counters table:

$5,000 \times 1,000 = 5,000,000\text{ rows}$

INSERT INTO topics (name, parent_id, type)
SELECT 
    CONCAT('topic-', subcategory_id, '-', counter_id) AS name,
    subcategory_id AS parent_id,
    3 AS type
FROM (
    SELECT 
        subcat.id AS subcategory_id,
        cnt.id AS counter_id
    FROM (
        SELECT id FROM topics WHERE type = 2
    ) AS subcat
    CROSS JOIN counters cnt
) AS topic_matrix;

Execution Profile:

• Rows inserted: 5,000,000
• Execution time: ~80 to 83 seconds (tested on MySQL 8.0)

---

Performance Breakdown & Timing

Generation Step	Source Calculation	Rows Added	Elapsed Time
1. Counters Table	$10 \times 10 \times 10$	1,000	< 0.05s
2. Categories	LIMIT 50	50	0.13s
3. Subcategories	$50 \times 100$	5,000	0.56s
4. Topics	$5,000 \times 1,000$	5,000,000	~82.0s
Total Pipeline		5,005,050	~83 seconds

---

Why Pure SQL Outperforms External Scripts

1. Elimination of I/O Bound Bottlenecks: External scripts stream data over TCP. Writing 5 million records even in batches of 1,000 entails 5,000 network round-trips. In pure SQL, operations run entirely inside the DB process memory space.
2. Zero Client-Side State: By deriving parent IDs via subqueries (SELECT id FROM topics WHERE type = 2), the database resolves foreign key relations dynamically during query plan execution.
3. Engine-Level Pipeline Optimization: The storage engine (InnoDB) can stream row generation directly into buffer pools and append to redo logs sequentially without needing continuous query parsing and parameter binding.

Key Takeaways

• Cartesian joins are not just query anti-patterns: While unintended cross joins cause production outages, intentional cross joins are one of the most potent data amplification techniques in relational computing.
• INSERT INTO ... SELECT avoids client overhead: When generating bulk data or running ETL transformations, streaming the output of a SELECT directly into an INSERT bypasses the network layer entirely.
• Seed tables unlock combinatorial generation: A simple table containing 10 single-digit integers allows you to generate millions of permutations, unique identifiers, and deterministic sequences entirely within standard SQL.