VtY3eDVP

1CREATE TABLE IF NOT EXISTS user (
2    id              INT PRIMARY KEY AUTO_INCREMENT,
3    name            VARCHAR(32) NOT NULL
4);
5
6CREATE TABLE IF NOT EXISTS tweet(
7    id              INT PRIMARY KEY AUTO_INCREMENT,
8    user_id         INT NOT NULL  REFERENCES user(id),
9    tweeted_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
10    favorite_num    INT NOT NULL DEFAULT 0
11);
12
13CREATE TABLE IF NOT EXISTS sentence_tweet(
14    id              INT PRIMARY KEY REFERENCES tweet(id),
15    sentence        VARCHAR(140) NOT NULL
16);
17
18CREATE TABLE IF NOT EXISTS reply_tweet(
19    id              INT PRIMARY KEY REFERENCES sentence_tweet(id),
20    reply_to        INT NOT NULL REFERENCES sentence_tweet(id)
21);
22
23CREATE TABLE IF NOT EXISTS retweet (
24    id              INT PRIMARY KEY REFERENCES tweet(id),
25    base_tweet_id   INT NOT NULL REFERENCES sentence_tweet(id)
26);
27
28-- まずuserに128万件突っ込む
29INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32);           # 1
30INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 2
31INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 4
32INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 8
33INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 16
34INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 32
35INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 64
36INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 128
37INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 256
38INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 512 
39INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 1024 
40INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 2048
41INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 4096 
42INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 8192
43INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 16384
44INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 32768
45INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 65536
46INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 131072 
47INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 262144 
48INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 524288
49INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; # 1048576
50INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user LIMIT 231424; # 1280000
51
52-- tweetに128万件突っ込む
53INSERT INTO tweet(user_id) SELECT ceil(rand() * 10000) FROM user;
54-- つぶやきのタイミングをばらけさせる
55UPDATE tweet SET tweeted_at = date_add('2000-01-01', interval ceil(rand() * 1000000) second);
56-- 日付のソートのためにインデックスを貼る
57ALTER TABLE tweet ADD INDEX tweeted_at_idx(tweeted_at);
58
59-- 余分なuserを削除する
60DELETE FROM user WHERE id > 10000;
61
62-- sentence_tweetに112万件突っ込む(order by randが遅いけど気にしない)
63INSERT INTO sentence_tweet(id, sentence) SELECT id, sha2(md5(rand()), 512) FROM tweet ORDER BY rand() LIMIT 1120000;
64
65-- reply_tweetに48万件突っ込む
66INSERT INTO reply_tweet(id, reply_to) 
67    SELECT s1.id, s2.id FROM 
68        (SELECT @i:=@i+1 AS row_num, id FROM (SELECT @i:=0) AS ROWNUM, 
69            (SELECT id FROM sentence_tweet ORDER BY rand() LIMIT 480000) AS st1) AS s1
70        JOIN
71        (SELECT @j:=@j+1 AS row_num, id FROM (SELECT @j:=0) AS ROWNUM, 
72            (SELECT id FROM sentence_tweet ORDER BY rand() LIMIT 480000) AS st2) AS s2
73        ON s1.row_num = s2.row_num;
74
75-- retweetに16万件突っ込む
76INSERT INTO retweet(id, base_tweet_id)
77    SELECT t.id, s.id FROM
78        (SELECT @i:=@i+1 AS row_num, id FROM (SELECT @i:=0) AS ROWNUM,
79            (SELECT id FROM tweet WHERE id NOT IN (SELECT id FROM sentence_tweet)) AS t1 ) AS t
80        JOIN
81        (SELECT @j:=@j+1 AS row_num, id FROM (SELECT @j:=0) AS ROWNUM, 
82            (SELECT id FROM sentence_tweet ORDER BY rand() LIMIT 480000) AS st) AS s
83        ON t.row_num = s.row_num;