スロークエリ1個目

[okashoi]

1個目 https://github.com/BBCapMegane/waiwai_isucon5/blob/8e3d4c5183e8ef624b3f2ed34592c719289f65b4/webapp/go/app.go#L673

order by する必要なさそう of the year one, another にindexはる？

    rows, err = db.Query(`SELECT * FROM relations WHERE one = ? OR another = ? ORDER BY created_at DESC`, user.ID, user.ID)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    friendsMap := make(map[int]time.Time)
    for rows.Next() {
        var id, one, another int
        var createdAt time.Time
        checkErr(rows.Scan(&id, &one, &another, &createdAt))
        var friendID int
        if one == user.ID {
            friendID = another
        } else {
            friendID = one
        }
        if _, ok := friendsMap[friendID]; !ok {
            friendsMap[friendID] = createdAt
        }
    }
    friends := make([]Friend, 0, len(friendsMap))
    for key, val := range friendsMap {
        friends = append(friends, Friend{key, val})
    }
    rows.Close()

_Originally posted by @ShuzoN in https://github.com/BBCapMegane/waiwai_isucon5/issues/15#issuecomment-500105524_

[ShuzoN]

r.HandleFunc("/", myHandler(GetIndex))

https://github.com/BBCapMegane/waiwai_isucon5/blob/master/webapp/go/app.go#L781-L782

[ShuzoN]

GetIndex かなりデカくね？

func GetIndex(w http.ResponseWriter, r *http.Request) {
    if !authenticated(w, r) {
        return
    }

    user := getCurrentUser(w, r)

    prof := Profile{}
    row := db.QueryRow(`SELECT * FROM profiles WHERE user_id = ?`, user.ID)
    err := row.Scan(&prof.UserID, &prof.FirstName, &prof.LastName, &prof.Sex, &prof.Birthday, &prof.Pref, &prof.UpdatedAt)
    if err != sql.ErrNoRows {
        checkErr(err)
    }

    rows, err := db.Query(`SELECT * FROM entries WHERE user_id = ? ORDER BY created_at LIMIT 5`, user.ID)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    entries := make([]Entry, 0, 5)
    for rows.Next() {
        var id, userID, private int
        var body string
        var createdAt time.Time
        checkErr(rows.Scan(&id, &userID, &private, &body, &createdAt))
        entries = append(entries, Entry{id, userID, private == 1, strings.SplitN(body, "\n", 2)[0], strings.SplitN(body, "\n", 2)[1], createdAt})
    }
    rows.Close()

    rows, err = db.Query(`SELECT c.id AS id, c.entry_id AS entry_id, c.user_id AS user_id, c.comment AS comment, c.created_at AS created_at
FROM comments c
JOIN entries e ON c.entry_id = e.id
WHERE e.user_id = ?
ORDER BY c.created_at DESC
LIMIT 10`, user.ID)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    commentsForMe := make([]Comment, 0, 10)
    for rows.Next() {
        c := Comment{}
        checkErr(rows.Scan(&c.ID, &c.EntryID, &c.UserID, &c.Comment, &c.CreatedAt))
        commentsForMe = append(commentsForMe, c)
    }
    rows.Close()

    rows, err = db.Query(`SELECT * FROM entries ORDER BY created_at DESC LIMIT 1000`)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    entriesOfFriends := make([]Entry, 0, 10)
    for rows.Next() {
        var id, userID, private int
        var body string
        var createdAt time.Time
        checkErr(rows.Scan(&id, &userID, &private, &body, &createdAt))
        if !isFriend(w, r, userID) {
            continue
        }
        entriesOfFriends = append(entriesOfFriends, Entry{id, userID, private == 1, strings.SplitN(body, "\n", 2)[0], strings.SplitN(body, "\n", 2)[1], createdAt})
        if len(entriesOfFriends) >= 10 {
            break
        }
    }
    rows.Close()

    rows, err = db.Query(`SELECT * FROM comments ORDER BY created_at DESC LIMIT 1000`)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    commentsOfFriends := make([]Comment, 0, 10)
    for rows.Next() {
        c := Comment{}
        checkErr(rows.Scan(&c.ID, &c.EntryID, &c.UserID, &c.Comment, &c.CreatedAt))
        if !isFriend(w, r, c.UserID) {
            continue
        }
        row := db.QueryRow(`SELECT * FROM entries WHERE id = ?`, c.EntryID)
        var id, userID, private int
        var body string
        var createdAt time.Time
        checkErr(row.Scan(&id, &userID, &private, &body, &createdAt))
        entry := Entry{id, userID, private == 1, strings.SplitN(body, "\n", 2)[0], strings.SplitN(body, "\n", 2)[1], createdAt}
        if entry.Private {
            if !permitted(w, r, entry.UserID) {
                continue
            }
        }
        commentsOfFriends = append(commentsOfFriends, c)
        if len(commentsOfFriends) >= 10 {
            break
        }
    }
    rows.Close()

    rows, err = db.Query(`SELECT * FROM relations WHERE one = ? OR another = ? ORDER BY created_at DESC`, user.ID, user.ID)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    friendsMap := make(map[int]time.Time)
    for rows.Next() {
        var id, one, another int
        var createdAt time.Time
        checkErr(rows.Scan(&id, &one, &another, &createdAt))
        var friendID int
        if one == user.ID {
            friendID = another
        } else {
            friendID = one
        }
        if _, ok := friendsMap[friendID]; !ok {
            friendsMap[friendID] = createdAt
        }
    }
    friends := make([]Friend, 0, len(friendsMap))
    for key, val := range friendsMap {
        friends = append(friends, Friend{key, val})
    }
    rows.Close()

    rows, err = db.Query(`SELECT user_id, owner_id, DATE(created_at) AS date, MAX(created_at) AS updated
FROM footprints
WHERE user_id = ?
GROUP BY user_id, owner_id, DATE(created_at)
ORDER BY updated DESC
LIMIT 10`, user.ID)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    footprints := make([]Footprint, 0, 10)
    for rows.Next() {
        fp := Footprint{}
        checkErr(rows.Scan(&fp.UserID, &fp.OwnerID, &fp.CreatedAt, &fp.Updated))
        footprints = append(footprints, fp)
    }
    rows.Close()

    render(w, r, http.StatusOK, "index.html", struct {
        User              User
        Profile           Profile
        Entries           []Entry
        CommentsForMe     []Comment
        EntriesOfFriends  []Entry
        CommentsOfFriends []Comment
        Friends           []Friend
        Footprints        []Footprint
    }{
        *user, prof, entries, commentsForMe, entriesOfFriends, commentsOfFriends, friends, footprints,
    })
}

[ShuzoN]

mysql> show create table relations\G
*************************** 1. row ***************************
       Table: relations
Create Table: CREATE TABLE `relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `one` int(11) NOT NULL,
  `another` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `friendship` (`one`,`another`)
) ENGINE=InnoDB AUTO_INCREMENT=500101 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[ShuzoN]

https://github.com/BBCapMegane/waiwai_isucon5/blob/8e3d4c5183e8ef624b3f2ed34592c719289f65b4/webapp/go/app.go#L715

ここでindex貼ってみる

[ShuzoN]

mysql> mysql> explain SELECT * FROM relations WHERE one = 1 OR another = 1 ORDER BY created_at DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | relations | NULL       | ALL  | friendship    | NULL | NULL    | NULL | 422515 |    10.02 | Using where; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

[ShuzoN]

index貼るだけでかなり早くなりそうな気がする

[ShuzoN]

alter table relations add index bakusoku_index_one(one);
alter table relations add index bakusoku_index_another(another);

[okashoi]

爆速になりそう

[ShuzoN]

1.4倍にした。close

[ShuzoN]

mysql> explain SELECT * FROM relations WHERE one = 1 OR another = 1 ORDER BY created_at DESC;
+----+-------------+-----------+------------+-------------+------------------------------------------------------+-------------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+
| id | select_type | table     | partitions | type        | possible_keys                                        | key                                       | key_len | ref  | rows | filtered | Extra                                                                               |
+----+-------------+-----------+------------+-------------+------------------------------------------------------+-------------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+
|  1 | SIMPLE      | relations | NULL       | index_merge | friendship,bakusoku_index_one,bakusoku_index_another | bakusoku_index_one,bakusoku_index_another | 4,4     | NULL |  208 |   100.00 | Using union(bakusoku_index_one,bakusoku_index_another); Using where; Using filesort |
+----+-------------+-----------+------------+-------------+------------------------------------------------------+-------------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)