⌨️

procedures

team_member1

create
    definer= root@`%`procedureloopInsertTeamMember1()
BEGIN
		DECLARE i INT DEFAULT 1;

		WHILE i <= 1000
				DO
            INSERT INTOteam_member(team_id, member_id, created_at, updated_at)
						VALUES(i, 1,now(),now());
						SET i = i + 1;
				END WHILE;
END;
SQL
복사

team_member2

create
    definer = root@`%` procedure loopInsertTeamMember2()
BEGIN
    DECLARE i INT DEFAULT 2;

    WHILE i <= 500
        DO
            INSERT INTO team_member(team_id, member_id, created_at, updated_at)
            VALUES (((i * 4 - 4) % 1000) + 1, i, now(), now()),
                   (((i * 4 - 3) % 1000) + 1, i, now(), now()),
                   (((i * 4 - 2) % 1000) + 1, i, now(), now()),
                   (((i * 4 - 1) % 1000) + 1, i, now(), now());
            SET i = i + 1;
        END WHILE;
END;
SQL
복사

poll

각 팀(i) 마다 30개 (j) 의 투표 생성

create
    definer = root@`%` procedure loopInsertPoll()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    WHILE i <= 1000
        DO
            WHILE j <= 30
                DO
                    INSERT INTO poll(team_id, host_id, title, allowed_poll_count, is_anonymous, status, created_at,
                                     updated_at, closed_at, code)
                    VALUES (i, 1, 'title', 3, false, 'OPEN', now(), now(), now(), concat(30 * i +j, j)),
                           (i, 1, 'title', 3, false, 'OPEN', now(), now(), now(), concat(30 * i +j+1, j)),
                           (i, 1, 'title', 3, false, 'OPEN', now(), now(), now(), concat(30 * i +j+2, j));
                    SET j = j + 3;
                END WHILE;
            SET i = i + 1;
            SET j = 1;
        END WHILE;
END;
SQL
복사

총 23초

poll_item

각 투표(i)마다 투표 항목 3개씩 생성

DROP PROCEDURE If EXISTS loopInsertPollItem;

DELETE FROM poll_item;
ALTER TABLE poll_item AUTO_INCREMENT=1;

DELIMITER $$
CREATE PROCEDURE loopInsertPollItem()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 30000 DO
            INSERT INTO poll_item(poll_id, subject, created_at, updated_at)
            VALUES (i, concat('subject-a', i), now(), now()),
                   (i, concat('subject-b', i), now(), now()),
                   (i, concat('subject-c', i), now(), now());
            SET i = i + 1;
        END WHILE;
END$$

CALL loopInsertPollItem();
SQL
복사

총 1분 20초

select_member

각 투표 항목(i)마다 멤버 1, 2가 투표

DROP PROCEDURE If EXISTS loopInsertSelectMember;

DELETE FROM select_member;
ALTER TABLE select_member AUTO_INCREMENT=1;

DELIMITER $$
CREATE PROCEDURE loopInsertSelectMember()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 90000 DO
            INSERT INTO select_member(poll_item_id, description, member_id)
            VALUES (i, concat('description-a', i), 1),
                   (i, concat('description-b', i), 2);
            SET i = i + 1;
        END WHILE;
END$$

CALL loopInsertSelectMember();
SQL
복사

총 4분 44초