xtgxVP1W

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    sentence        VARCHAR(140),
12    base_tweet_id   INT REFERENCES tweet(id),
13    reply_to        INT REFERENCES tweet(id)
14);
15
16INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32);           -- 1
17INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 2
18INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 4
19INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 8
20INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 16
21INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 32
22INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 64
23INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 128
24INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 256
25INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 512 
26INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 1024 
27INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 2048
28INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 4096 
29INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 8192
30INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 16384
31INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 32768
32INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 65536
33INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 131072 
34INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 262144 
35INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 524288
36INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user; -- 1048576
37INSERT INTO user(name) SELECT SUBSTRING(SHA1(rand()), 1, 32) FROM user LIMIT 231424; -- 1280000
38
39-- 普通のつぶやき
40INSERT INTO tweet(user_id, sentence) SELECT ceil(rand() * 10000), sha2(md5(rand()), 512) FROM user LIMIT 640000;
41
42-- 返信
43INSERT INTO tweet(user_id, sentence, reply_to) SELECT ceil(rand() * 10000), sha2(md5(rand()), 512), id FROM tweet ORDER BY rand() LIMIT 480000;
44
45-- リツイート
46INSERT INTO tweet(user_id, base_tweet_id) SELECT ceil(rand() * 10000), id FROM tweet ORDER BY rand() LIMIT 160000;
47
48-- つぶやきのタイミングをばらけさせる
49UPDATE tweet SET tweeted_at = date_add('2000-01-01', interval ceil(rand() * 1000000) second);
50-- 日付のソートのためにインデックスを貼る
51ALTER TABLE tweet ADD INDEX tweeted_at_idx(tweeted_at);
52-- 余分なユーザを削除
53DELETE FROM user WHERE id > 10000;