tennant_db player_score 容量削減

[tetsuzawa]

sqlite> select count(*) from 76_buckup.db;
Error: in prepare, unrecognized token: "76_buckup" (1)
sqlite> select count(*) from player_score;
24713
sqlite> CREATE TABLE player_score_tmp AS SELECT player_id, competition_id, MAX(row_num) AS row_num FROM player_score GROUP BY player_id, competition_id;
sqlite> select count(*) from player_score_tmp ;
1299
sqlite> CREATE TABLE player_score_new AS SELECT player_score.* FROM player_score JOIN player_score_tmp USING(player_id, competition_id, row_num);
sqlite> select count(*) from player_score_new ;
1299
sqlite> DROP TABLE player_score;
sqlite> DROP TABLE player_score_tmp;
sqlite> ALTER TABLE player_score_new RENAME TO player_score;
sqlite> VACUUM;

[tetsuzawa]

$ cp -r initial_data initial_data_buckup

$ sh reduce_player_score.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

[tetsuzawa]

11:27:41.217328 SCORE: 5369 (+5369 0(0%))
[ADMIN] 11:27:41.217464 score.ScoreTable{
  "GET /api/admin/tenants/billing":                         48,
  "GET /api/organizer/billing":                             37,
  "GET /api/organizer/players/list":                        50,
  "GET /api/player/competition/:competition_id/ranking":    1266,
  "GET /api/player/competitions":                           230,
  "GET /api/player/player/:player_name":                    1281,
  "POST /api/admin/tenants/add":                            4,
  "POST /api/organizer/competition/:competition_id/finish": 60,
  "POST /api/organizer/competition/:competition_id/score":  75,
  "POST /api/organizer/competitions/add":                   64,
  "POST /api/organizer/player/:player_name/disqualified":   14,
  "POST /api/organizer/players/add":                        8,
}