Game details page loads really slowly when you're logged in

[dfabulich]

I updated util.php to modify mysql_query to add performance logging:

function mysql_query($query, $db) { global $i; $j = $i++; $start = microtime(true); error_log("$j $start $query"); $result = mysqli_query($db, $query); error_log("$j end " . (microtime(true) - $start) . " $query"); return $result; }

Then I tried loading Counterfeit Monkey http://localhost:8080/viewgame?id=aearuuxv83plclpl logged in and logged out. It was night and day; when logged out, the page started loading basically instantly. When logged in as ifdbadmin, the page took 12+ seconds.

It's not quite that bad on production ifdb, maybe 4 seconds? but it's bad.

The big bad query appears to be this one that suggests other recommended games.

// Look for other games to recommend - these are games that
// were given 4- or 5-star ratings by members who also gave
// the current game a 4- or 5-star rating, AND which games
// are unrated AND unplayed by the current user.
//
// If we're logged in, make sure the ratings are coming from
// other users (don't bother generating recommendations based
// on the current user's own ratings, for obvious reasons).
// Also exclude games that are already on the current user's
// play list or wish list - they obviously already know about
// these games, so there's no reason to recommend them.

Here's how that query ran on my machine:

explain extended select
    games.id as id,
    games.title as title,
    games.author as author,
    games.`desc` as `desc`,
    (games.coverart is not null) as hasart
from
    (games,
    reviews as r1,
    reviews as r2)
    left outer join reviews as r3
        on r3.gameid = r2.gameid  and r3.id != r2.id
        and r3.userid = '0000000000000000' left outer join playedgames as pg
        on pg.userid = '0000000000000000'
        and pg.gameid = r2.gameid left outer join wishlists as wl
        on wl.userid = '0000000000000000'
        and wl.gameid = r2.gameid left outer join unwishlists as uw
        on uw.userid = '0000000000000000'  and uw.gameid = r2.gameid
where
    r1.gameid = '0dbnusxunq7fw5ro'
    and r1.special is null and r2.special is null
    and r1.rating >= 4
    and r2.gameid != r1.gameid
    and r2.rating >= 4
    and r1.userid = r2.userid
    and games.id = r2.gameid
    and ifnull(now() >= r1.embargodate, 1)
    and ifnull(now() >= r2.embargodate, 1)
    and not (games.flags & 1)
    and r1.userid != '0000000000000000'
    and r2.userid != '0000000000000000'
    and r3.id is null
    and ifnull(now() >= r3.embargodate, 1)
    and pg.userid is null
    and wl.userid is null
    and uw.userid is null
group by r2.gameid
order by rand()
limit 0, 3

+------+-------------+-------+------------+---------------+---------------+---------+----------------+--------+----------+---------------------------------------------------------------------+
| id   | select_type | table | type       | possible_keys | key           | key_len | ref            | rows   | filtered | Extra                                                               |
+------+-------------+-------+------------+---------------+---------------+---------+----------------+--------+----------+---------------------------------------------------------------------+
|    1 | SIMPLE      | r1    | ref        | userid,gameid | gameid        | 34      | const          | 126    |    99.99 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | r2    | ref        | userid,gameid | userid        | 34      | ifdb.r1.userid | 19     |   100.00 | Using where                                                         |
|    1 | SIMPLE      | r3    | ref|filter | userid,gameid | gameid|userid | 34|34   | ifdb.r2.gameid | 8 (0%) |     0.01 | Using where; Not exists; Using rowid filter                         |
|    1 | SIMPLE      | pg    | ref        | userid,gameid | userid        | 34      | const          | 1      |   100.00 | Using where; Not exists                                             |
|    1 | SIMPLE      | wl    | ref        | gameid,userid | userid        | 34      | const          | 1      |   100.00 | Using where; Not exists                                             |
|    1 | SIMPLE      | uw    | ref|filter | gameid,userid | gameid|userid | 34|34   | ifdb.r2.gameid | 1 (0%) |     0.16 | Using where; Not exists; Using rowid filter                         |
|    1 | SIMPLE      | games | ALL        | PRIMARY       | NULL          | NULL    | NULL           | 10829  |   100.00 | Using where; Using join buffer (flat, BNL join)                     |
+------+-------------+-------+------------+---------------+---------------+---------+----------------+--------+----------+---------------------------------------------------------------------+

[salty-horse]

It's not just a game details page. The main page is also slow to load (at least for the first time in some period of time, since it caches the results).

[dfabulich]

I think the thing to do here is to load recommendations with a separate AJAX request when you're logged in. That way, you can see the important parts of the page ASAP. (I think that applies both to the game details page and the home page.)

[salty-horse]

If server processing is a concern, the recommendations can also be stored somewhere, like the browser's localStorage.