search

sunakan / isunarabe13-suburi

0 stars 0 forks source link

分析-008(9975→xxxx) #9

Open sunakan opened 14 hours ago

sunakan commented 14 hours ago

続き

スコア：9975

sunakan commented 14 hours ago 
+-------+--------+--------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                 URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+--------------------------------------+--------+--------+--------+---------+
| 578   | GET    | /api/livestream/\d+/livecomment      | 0.004  | 0.252  | 1.136  | 145.417 |
| 609   | GET    | /api/livestream/\d+/reaction         | 0.004  | 0.230  | 1.332  | 139.882 |
| 34    | POST   | /api/livestream/\d+/moderate         | 0.188  | 3.536  | 6.985  | 120.239 |
| 627   | POST   | /api/livestream/\d+/livecomment      | 0.004  | 0.153  | 0.632  | 95.801  |
| 5468  | GET    | /api/user/\w+/icon                   | 0.016  | 0.013  | 0.612  | 71.424  |
| 11    | GET    | /api/user/\w+/statistics             | 0.452  | 5.127  | 6.713  | 56.393  |
| 80    | GET    | /api/livestream/search               | 0.040  | 0.662  | 1.500  | 52.972  |
| 537   | POST   | /api/livestream/\d+/reaction         | 0.004  | 0.095  | 1.428  | 50.985  |
| 310   | POST   | /api/register                        | 0.004  | 0.149  | 0.884  | 46.096  |
| 125   | POST   | /api/livestream/reservation          | 0.004  | 0.367  | 0.884  | 45.821  |
| 309   | POST   | /api/icon                            | 0.012  | 0.122  | 0.621  | 37.757  |
| 315   | POST   | /api/login                           | 0.004  | 0.066  | 0.848  | 20.800  |
| 1     | POST   | /api/initialize                      | 13.704 | 13.704 | 13.704 | 13.704  |
| 108   | GET    | /api/tag                             | 0.004  | 0.080  | 0.664  | 8.680   |
| 212   | GET    | /api/livestream                      | 0.004  | 0.028  | 0.592  | 5.924   |
| 70    | GET    | /api/livestream/\d+/report           | 0.004  | 0.078  | 0.544  | 5.472   |
| 2     | GET    | /api/livestream/\d+/statistics       | 2.720  | 2.736  | 2.752  | 5.472   |
| 50    | GET    | /api/livestream/\d+/ngwords          | 0.008  | 0.099  | 0.388  | 4.932   |
| 57    | POST   | /api/livestream/\d+/enter            | 0.004  | 0.079  | 0.368  | 4.508   |
| 48    | DELETE | /api/livestream/\d+/exit             | 0.008  | 0.064  | 0.280  | 3.048   |
| 10    | GET    | /api/user/\w+/theme                  | 0.004  | 0.049  | 0.200  | 0.488   |
| 2     | GET    | /api/user/endoyui0/livestream        | 0.092  | 0.176  | 0.260  | 0.352   |
| 1     | GET    | /api/user/kazuyasato0/livestream     | 0.296  | 0.296  | 0.296  | 0.296   |
| 2     | GET    | /api/user/kazuya580/livestream       | 0.112  | 0.142  | 0.172  | 0.284   |
| 1     | GET    | /api/user/iyamamoto1/livestream      | 0.056  | 0.056  | 0.056  | 0.056   |
| 1     | GET    | /api/user/kanasasaki0/livestream     | 0.048  | 0.048  | 0.048  | 0.048   |
| 1     | GET    | /api/user/nakamuraryohei0/livestream | 0.040  | 0.040  | 0.040  | 0.040   |
| 1     | GET    | /api/user/csasaki0/livestream        | 0.036  | 0.036  | 0.036  | 0.036   |
| 1     | GET    | /api/payment                         | 0.004  | 0.004  | 0.004  | 0.004   |
| 3     | GET    | /api/user/me                         | 0.000  | 0.001  | 0.004  | 0.004   |
| 1     | GET    | /api/user/test                       | 0.004  | 0.004  | 0.004  | 0.004   |
| 1     | GET    | /api/livestream/\d+                  | 0.004  | 0.004  | 0.004  | 0.004   |
+-------+--------+--------------------------------------+--------+--------+--------+---------+
sunakan commented 14 hours ago 
+-------+--------+--------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                 URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+--------------------------------------+--------+--------+--------+---------+
| 578   | GET    | /api/livestream/\d+/livecomment      | 0.004  | 0.252  | 1.136  | 145.417 |
| 609   | GET    | /api/livestream/\d+/reaction         | 0.004  | 0.230  | 1.332  | 139.882 |
| 34    | POST   | /api/livestream/\d+/moderate         | 0.188  | 3.536  | 6.985  | 120.239 |
| 627   | POST   | /api/livestream/\d+/livecomment      | 0.004  | 0.153  | 0.632  | 95.801  |
| 5468  | GET    | /api/user/\w+/icon                   | 0.016  | 0.013  | 0.612  | 71.424  |

GETが上位にきているので、カイゼンの余地ありそう

sunakan commented 14 hours ago 
pt-query-digest --limit 10 tmp/analysis/latest/mysql-slow.log.*

# 19.1s user time, 120ms system time, 52.23M rss, 392.13G vsz
# Current date: Mon Sep 23 00:27:16 2024
# Hostname: CWPC20001.local
# Files: tmp/analysis/latest/mysql-slow.log.isu-1, tmp/analysis/latest/mysql-slow.log.isu-2, tmp/analysis/latest/mysql-slow.log.isu-3
# Overall: 451.35k total, 94 unique, 4.47k QPS, 2.97x concurrency ________
# Time range: 2024-09-22T15:17:57 to 2024-09-22T15:19:38
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           300s     1us   408ms   664us     2ms     3ms   152us
# Lock time             1s       0    17ms     2us     1us    75us     1us
# Rows sent        398.20k       0   7.32k    0.90    0.99   20.67    0.99
# Rows examine      20.95M       0  14.03k   48.66    6.98  717.28    0.99
# Query size        66.08M       5   1.94M  153.51  329.68   3.57k   42.48

# Profile
# Rank Query ID                            Response time Calls  R/Call V/M
# ==== =================================== ============= ====== ====== ===
#    1 0x4ADE2DC90689F1C4891749AF54FB8D14  39.4575 13.2%  51187 0.0008  0.00 DELETE SELECT livecomments
#    2 0xFBC5564AE716EAE82F20BFB45F6C37E7  27.9182  9.3%  69338 0.0004  0.00 SELECT tags
#    3 0x84B457C910C4A79FC9EBECB8B1065C66  24.9622  8.3%  28556 0.0009  0.00 SELECT icons
#    4 0x64CC8A4E8E4B390203375597CE4D611F  21.9347  7.3%    573 0.0383  0.01 SELECT ng_words
#    5 0xFFFCA4D67EA0A788813031B8BBC3B329  19.9211  6.6%   4374 0.0046  0.01 COMMIT
#    6 0x59F1B6DD8D9FEC059E55B3BFD624E8C3  18.8240  6.3%   1090 0.0173  0.01 SELECT reservation_slots
#    7 0x22279D81D51006139E0C76405B54C261  14.8477  5.0%  28114 0.0005  0.00 SELECT domains domainmetadata
#    8 0x38BC86A45F31C6B1EE324671506C898A  13.9448  4.7%  27386 0.0005  0.00 SELECT themes
#    9 0x42EF7D7D98FBCC9723BF896EBFC51D24  12.4449  4.2%  22510 0.0006  0.00 SELECT records
#   10 0xEA1E6309EEEFF9A6831AD2FB940FC23C  11.5867  3.9%  27068 0.0004  0.00 SELECT users
# MISC 0xMISC                              93.9169 31.3% 191155 0.0005   0.0 <84 ITEMS>

# Query 1: 853.12 QPS, 0.66x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 105821212
# Scores: V/M = 0.00
# Time range: 2024-09-22T15:18:38 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11   51187
# Exec time     13     39s    98us    30ms   770us     3ms     1ms   236us
# Lock time     14   178ms       0     8ms     3us     1us    78us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  99.99k       2       4    2.00    1.96    0.03    1.96
# Query size    24  16.22M     263     535  332.36  381.65   27.04  313.99
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ##################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
DELETE FROM livecomments
            WHERE
            id = 1005 AND
            livestream_id = 7534 AND
            (SELECT COUNT(*)
            FROM
            (SELECT 'あの敵、どうやって避けてるんですか？' AS text) AS texts
            INNER JOIN
            (SELECT CONCAT('%', '神経工学', '%')    AS pattern) AS patterns
            ON texts.text LIKE patterns.pattern) >= 1\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from  livecomments
            WHERE
            id = 1005 AND
            livestream_id = 7534 AND
            (SELECT COUNT(*)
            FROM
            (SELECT 'あの敵、どうやって避けてるんですか？' AS text) AS texts
            INNER JOIN
            (SELECT CONCAT('%', '神経工学', '%')    AS pattern) AS patterns
            ON texts.text LIKE patterns.pattern) >= 1\G

# Query 2: 1.05k QPS, 0.42x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 40555125
# Scores: V/M = 0.00
# Time range: 2024-09-22T15:18:32 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15   69338
# Exec time      9     28s    43us    36ms   402us     2ms   820us   125us
# Lock time     21   266ms       0     7ms     3us     1us    72us     1us
# Rows sent     17  67.71k       1       1       1       1       0       1
# Rows examine   0  67.71k       1       1       1       1       0       1
# Query size     3   2.11M      31      33   31.95   31.70    0.47   31.70
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ##########################################################
# 100us  ################################################################
#   1ms  #############
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'tags'\G
#    SHOW CREATE TABLE `isupipe`.`tags`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM tags WHERE id = 62\G

# Query 3: 391.18 QPS, 0.34x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 148783078
# Scores: V/M = 0.00
# Time range: 2024-09-22T15:18:25 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   28556
# Exec time      8     25s    68us    34ms   874us     3ms     1ms   332us
# Lock time      6    75ms       0     3ms     2us     1us    37us     1us
# Rows sent      5  23.86k       0       1    0.86    0.99    0.35    0.99
# Rows examine   0  23.86k       0       1    0.86    0.99    0.35    0.99
# Query size     1   1.19M      41      44   43.86   42.48    0.23   42.48
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ####
# 100us  ################################################################
#   1ms  #######################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'icons'\G
#    SHOW CREATE TABLE `isupipe`.`icons`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT image FROM icons WHERE user_id = 1016\G

# Query 4: 8.19 QPS, 0.31x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 26698340
# Scores: V/M = 0.01
# Time range: 2024-09-22T15:18:28 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     573
# Exec time      7     22s     4ms   122ms    38ms    75ms    20ms    34ms
# Lock time      0     3ms       0     2ms     4us     1us    84us     1us
# Rows sent      0     229       0       2    0.40    0.99    0.57       0
# Rows examine  37   7.84M  14.00k  14.03k  14.02k  13.78k       0  13.78k
# Query size     0  55.39k      97      99   98.99   97.36    0.27   97.36
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'ng_words'\G
#    SHOW CREATE TABLE `isupipe`.`ng_words`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = 1014 AND livestream_id = 7524\G

# Query 5: 59.92 QPS, 0.27x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 145646452
# Scores: V/M = 0.01
# Time range: 2024-09-22T15:18:25 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    4374
# Exec time      6     20s    23us    89ms     5ms    14ms     6ms     3ms
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0  25.63k       6       6       6       6       0       6
# String:
# Databases    isupipe (4065/92%), isudns (309/7%)
# Hosts        localhost
# Users        isucon (4065/92%), isudns (309/7%)
# Query_time distribution
#   1us
#  10us  #####################################
# 100us  ##############
#   1ms  ################################################################
#  10ms  #################
# 100ms
#    1s
#  10s+
commit\G

# Query 6: 14.93 QPS, 0.26x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 56825988
# Scores: V/M = 0.01
# Time range: 2024-09-22T15:18:25 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1090
# Exec time      6     19s     2ms    80ms    17ms    42ms    13ms    13ms
# Lock time      0     8ms       0     7ms     7us     1us   197us     1us
# Rows sent      0   1.06k       1       1       1       1       0       1
# Rows examine  43   9.11M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  91.54k      86      86      86      86       0      86
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###################################
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reservation_slots'\G
#    SHOW CREATE TABLE `isupipe`.`reservation_slots`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT slot FROM reservation_slots WHERE start_at = 1701126000 AND end_at = 1701129600\G

# Query 7: 323.15 QPS, 0.17x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 120682509
# Scores: V/M = 0.00
# Time range: 2024-09-22T15:18:11 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   28114
# Exec time      4     15s    83us    33ms   528us     2ms   902us   194us
# Lock time      8   103ms       0    13ms     3us     1us   112us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     4   3.03M     113     113     113     113       0     113
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ##########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'domains'\G
#    SHOW CREATE TABLE `isudns`.`domains`\G
#    SHOW TABLE STATUS FROM `isudns` LIKE 'domainmetadata'\G
#    SHOW CREATE TABLE `isudns`.`domainmetadata`\G
# EXPLAIN /*!50100 PARTITIONS*/
select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='t.isucon.pw'\G

# Query 8: 375.15 QPS, 0.19x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 117744678
# Scores: V/M = 0.00
# Time range: 2024-09-22T15:18:25 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   27386
# Exec time      4     14s    74us    26ms   509us     2ms   919us   167us
# Lock time      6    77ms       0     5ms     2us     1us    51us     1us
# Rows sent      6  26.74k       1       1       1       1       0       1
# Rows examine   0  26.74k       1       1       1       1       0       1
# Query size     1   1.07M      38      41   40.90   40.45    0.56   40.45
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  ####
# 100us  ################################################################
#   1ms  ##########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'themes'\G
#    SHOW CREATE TABLE `isupipe`.`themes`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM themes WHERE user_id = 1054\G

# Query 9: 258.74 QPS, 0.14x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 31809272
# Scores: V/M = 0.00
# Time range: 2024-09-22T15:18:11 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   22510
# Exec time      4     12s    90us    26ms   552us     2ms   932us   204us
# Lock time      4    60ms       0     7ms     2us     1us    54us     1us
# Rows sent      0   2.19k       0       1    0.10    0.99    0.30       0
# Rows examine   0   2.19k       0       1    0.10    0.99    0.30       0
# Query size     4   3.04M     128     214  141.43  151.03   11.68  136.99
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ##########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='pipe.t.isucon.pw' and domain_id=1\G

# Query 10: 370.79 QPS, 0.16x concurrency, ID 0xEA1E6309EEEFF9A6831AD2FB940FC23C at byte 69834330
# Scores: V/M = 0.00
# Time range: 2024-09-22T15:18:25 to 2024-09-22T15:19:38
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5   27068
# Exec time      3     12s    51us    20ms   428us     2ms   869us   131us
# Lock time      7    88ms       0     6ms     3us     1us    64us     1us
# Rows sent      6  26.43k       1       1       1       1       0       1
# Rows examine   0  26.43k       1       1       1       1       0       1
# Query size     1 922.55k      32      35   34.90   34.95    0.54   34.95
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  #################################################
# 100us  ################################################################
#   1ms  ############
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM users WHERE id = 1021\G
sunakan commented 4 hours ago 
+-------+--------+--------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                 URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+--------------------------------------+--------+--------+--------+---------+
| 578   | GET    | /api/livestream/\d+/livecomment      | 0.004  | 0.252  | 1.136  | 145.417 |
git grep -r '/api/livestream/:livestream_id/livecomment'
rsync-webapp-go/main.go:178:    e.GET("/api/livestream/:livestream_id/livecomment", getLivecommentsHandler)
rsync-webapp-go/main.go:180:    e.POST("/api/livestream/:livestream_id/livecomment", postLivecommentHandler)
rsync-webapp-go/main.go:188:    e.POST("/api/livestream/:livestream_id/livecomment/:livecomment_id/report", reportLivecommentHandler)

自分用にtemp()等を記述しているため、行番号はオリジナルとはズレる

sunakan commented 4 hours ago 
func getLivecommentsHandler(c echo.Context) error {
        // ...
    query := "SELECT * FROM livecomments WHERE livestream_id = ? ORDER BY created_at DESC"
    if c.QueryParam("limit") != "" {
        // ...
        query += fmt.Sprintf(" LIMIT %d", limit)
    }
    livecommentModels := []LivecommentModel{}
    err = tx.SelectContext(ctx, &livecommentModels, query, livestreamID)
    if errors.Is(err, sql.ErrNoRows) { // 空なら空として返している
        return c.JSON(http.StatusOK, []*Livecomment{})
    }
    livecomments := make([]Livecomment, len(livecommentModels))
    for i := range livecommentModels {
        livecomment, err := fillLivecommentResponse(ctx, tx, livecommentModels[i])
        // ...
        livecomments[i] = livecomment
    }
        // ...
}

limitの有無があるらしい

type Livecomment struct {
    ID         int64      `json:"id"`
    User       User       `json:"user"`
    Livestream Livestream `json:"livestream"`
    Comment    string     `json:"comment"`
    Tip        int64      `json:"tip"`
    CreatedAt  int64      `json:"created_at"`
}
sunakan commented 4 hours ago 
func fillLivecommentResponse(ctx context.Context, tx *sqlx.Tx, livecommentModel LivecommentModel) (Livecomment, error) {
    commentOwnerModel := UserModel{}
    if err := tx.GetContext(ctx, &commentOwnerModel, "SELECT * FROM users WHERE id = ?", livecommentModel.UserID); err != nil {
        return Livecomment{}, err
    }
    commentOwner, err := fillUserResponse(ctx, tx, commentOwnerModel)
    if err != nil {
        return Livecomment{}, err
    }

    livestreamModel := LivestreamModel{}
    if err := tx.GetContext(ctx, &livestreamModel, "SELECT * FROM livestreams WHERE id = ?", livecommentModel.LivestreamID); err != nil {
        return Livecomment{}, err
    }
    livestream, err := fillLivestreamResponse(ctx, tx, livestreamModel)
    if err != nil {
        return Livecomment{}, err
    }

    livecomment := Livecomment{
        ID:         livecommentModel.ID,
        User:       commentOwner,
        Livestream: livestream,
        Comment:    livecommentModel.Comment,
        Tip:        livecommentModel.Tip,
        CreatedAt:  livecommentModel.CreatedAt,
    }

    return livecomment, nil
}
sunakan commented 4 hours ago
sunakan commented 3 hours ago 
type Livecomment struct {
    ID int64
    User type User struct { // usersテーブル
        ID int64
    }
    Livestream type Livestream struct { // livestreamsテーブル
        ID int64
    }
}

1つのレスポンスを作るには、3テーブル必要

これを3回クエリ叩いている 1つでも失敗したら早期リターンしている→joinしたら良さそう

違った

sunakan commented 3 hours ago 
type Livecomment struct {// livecommentsテーブル
    ID         int64      `json:"id"`
    User       User       `json:"user"`
    Livestream Livestream `json:"livestream"`
    Comment    string     `json:"comment"`
    Tip        int64      `json:"tip"`
    CreatedAt  int64      `json:"created_at"`
}

type User struct { // usersテーブル
    ID          int64  `json:"id"`
    Name        string `json:"name"`
    DisplayName string `json:"display_name,omitempty"`
    Description string `json:"description,omitempty"`
    Theme       Theme  `json:"theme,omitempty"`
    IconHash    string `json:"icon_hash,omitempty"`
}
type Theme struct { // themesテーブル
    ID       int64 `json:"id"`
    DarkMode bool  `json:"dark_mode"`
}

type Livestream struct {// livestreamsテーブル
    ID           int64  `json:"id"`
    Owner        User   `json:"owner"`
    Title        string `json:"title"`
    Description  string `json:"description"`
    PlaylistUrl  string `json:"playlist_url"`
    ThumbnailUrl string `json:"thumbnail_url"`
    Tags         []Tag  `json:"tags"` // tagsは空のときもあり得ると考える
    StartAt      int64  `json:"start_at"`
    EndAt        int64  `json:"end_at"`
}
type Tag struct { // タグテーブル
    ID   int64  `json:"id"`
    Name string `json:"name"`
}
sunakan commented 3 hours ago

これ見るとthemesテーブルとusersテーブルは1つにまとめても良さそうだけど、inner joinしたらいいだけなので、後半かも

image

issueに書き出しておく

sunakan commented 3 hours ago
image
sunakan commented 3 hours ago
  1. tags以外とtagsで2回クエリする
  2. leftjoinでtagsも一緒にひっぱる(1回のクエリで済ます)

どっちが早いんだろうか

sunakan commented 3 hours ago

とりあえず1でやる

そしてSQL記述する

sunakan commented 3 hours ago 
select *
from livecomments
inner join users on users.id = livecomments.user_id
inner join themes on themes.user_id = users.id
inner join icons on icons.user_id = users.id
inner join livestreams on livestreams.id = livecomments.livestream_id
inner join users as owners on owners.id = livestreams.user_id
inner join themes as owner_themes on owner_themes.user_id = owners.id
inner join icons as owner_icons on owner_icons.user_id = owners.id
where livecomments.livestream_id = 7534
;

ハッシュ値も事前計算して画像はもう引っ張らないとかも後で入れようか

sunakan commented 3 hours ago 
    query := "SELECT * FROM livecomments WHERE livestream_id = ? ORDER BY created_at DESC"

livecomments.livestream_id と livecomments.created_at desc で複数index貼って良いかも

そして livestream_idだけのindexをワンちゃん外す（ただ、このへんは最後）

sunakan commented 1 hour ago 
  , livestream_owner_icons.id as "livestream_owner_icon_id"
  , livestream_owner_icons.image as "livestream_owner_icon_image"
  , (select GROUP_CONCAT(CONCAT(tags.id, ':', tags.name) SEPARATOR ',') from livestream_tags inner join tags on livestream_tags.tag_id = tags.id where livestream_tags.livestream_id = livecomments.livestream_id) as "livestream_tags"
from livecomments

みたいなサブクエリできるわ

sunakan commented 1 hour ago

SQL

select
  livecomments.id as "livevcomment_id"
  , livecomments.comment as "livecomment_comment"
  , livecomments.tip as "livecomment_tip"
  , livecomments.created_at as "livecomment_created_at"
  , users.id as "user_id"
  , users.name as "user_name"
  , users.display_name as "user_display_name"
  , users.description as "user_description"
  , themes.id as "theme_id"
  , themes.dark_mode as "theme_dark_mode"
  , icons.id as "icon_id"
  , icons.image as "icon_image"
  , livestreams.id as "livestream_id"
  , livestreams.title as "livestream_title"
  , livestreams.description as "livestream_description"
  , livestreams.playlist_url as "livestream_playlist_url"
  , livestreams.thumbnail_url as "livestream_thumbnail_url"
  , livestreams.start_at as "livestream_start_at"
  , livestreams.end_at as "livestream_end_at"
  , livestream_owners.id as "livestream_owner_id"
  , livestream_owners.name as "livestream_owner_name"
  , livestream_owners.display_name as "livestream_owner_display_name"
  , livestream_owners.description as "livestream_owner_description"
  , livestream_owner_themes.id as "livestream_owner_theme_id"
  , livestream_owner_themes.dark_mode as "livestream_owner_theme_dark_mode"
  , livestream_owner_icons.id as "livestream_owner_icon_id"
  , livestream_owner_icons.image as "livestream_owner_icon_image"
  , (select CONCAT('[', GROUP_CONCAT(CONCAT('{"', tags.id, '":"', tags.name, '"}') SEPARATOR ','), ']') from livestream_tags inner join tags on livestream_tags.tag_id = tags.id where livestream_tags.livestream_id = livecomments.livestream_id) as "livestream_tags"
from livecomments
inner join users on users.id = livecomments.user_id
inner join themes on themes.user_id = users.id
inner join icons on icons.user_id = users.id
inner join livestreams on livestreams.id = livecomments.livestream_id
inner join users as livestream_owners on livestream_owners.id = livestreams.user_id
inner join themes as livestream_owner_themes on livestream_owner_themes.user_id = livestream_owners.id
inner join icons as livestream_owner_icons on livestream_owner_icons.user_id = livestream_owners.id
where livecomments.livestream_id = 7534
sunakan commented 1 hour ago

コメントの分だけ、tagsが冗長になるけど、これでいいかな

sunakan commented 1 hour ago

ということから、もしかしたら2回のクエリに分けたほうがいいかもしれないけど、これでやる

sunakan commented 1 hour ago

サブクエリは以下のほうが楽ということに気づいた

  , (select CONCAT('[', GROUP_CONCAT(CONCAT('{"id":', tags.id, ',"name":"', tags.name, '"}') SEPARATOR ','), ']') from livestream_tags inner join tags on livestream_tags.tag_id = tags.id where livestream_tags.livestream_id = livecomments.livestream_id) as "livestream_tags"
sunakan commented 1 hour ago

こんな感じ

[{"id":58,"name":"ペットと一緒"},{"id":34,"name":"夜ふかし"},{"id":102,"name":"サプライズ"},{"id":42,"name":"絵描き"},{"id":82,"name":"子育て"}]
sunakan commented 32 minutes ago

icon持たないユーザー(レコードがない)も居た

sunakan commented 31 minutes ago

leftjoinにしないといけない