Skip to content

Instantly share code, notes, and snippets.

@arpitbbhayani
Last active July 29, 2023 15:09
Show Gist options
  • Save arpitbbhayani/96a42c28d134871ebc11faad272b5349 to your computer and use it in GitHub Desktop.
Save arpitbbhayani/96a42c28d134871ebc11faad272b5349 to your computer and use it in GitHub Desktop.
Quickly populate random 5 million + rows in a taxonomy using SQL
# create table topics!
create table topics (
id int primary key auto_increment,
name varchar(256),
parent_id int null,
type smallint not null,
foreign key (parent_id) references topics(id),
index(type)
);
# create table counters
create table counters (
id char(8) primary key
);
# create table seed
create table seed (
id int primary key
);
# insert 0 to 9
insert into seed values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
# amplify counters to have values from 0 to 999
INSERT into counters
SELECT CONCAT(s1.id, s2.id, s3.id) as n from seed as s1, seed as s2, seed as s3;
# inserting 50 categories
INSERT INTO topics(name, parent_id, type)
SELECT CONCAT("cat-", counter_id), NULL, 1
FROM (
SELECT id as counter_id from counters limit 50
) t;
# inserting 50 * 100 sub-categories
INSERT INTO topics(name, parent_id, type)
SELECT CONCAT("subcat-", category_id, counter_id), category_id, 2
FROM (
select categories.id as category_id, counters.id as counter_id FROM
(
(SELECT id from counters limit 100) counters,
(SELECT id from topics where type = 1) categories
)
) t;
# inserting 50 * 100 * 1000 topics
INSERT INTO topics(name, parent_id, type)
SELECT CONCAT("topic-", subcategory_id, counter_id), subcategory_id, 3
FROM (
select subcategories.id as subcategory_id, counters.id as counter_id FROM
(
(SELECT id from counters limit 1000) counters,
(SELECT id from topics where type = 2) subcategories
)
) t;
select count(id) from topics;
select type, count(id) from topics group by type;
select type, min(parent_id), count(id) from topics group by type;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment