Slow Query: Cross recommendations

[dfabulich]

I'm not at all sure why this is so slow. It says Rows_examined: 39211, but it seems like it's using an index, and the sum of r_rows is under 300. 🤔

# User@Host: ifdb[ifdb] @ localhost [127.0.0.1]
# Thread_id: 6597950  Schema: ifdb  QC_hit: No
# Query_time: 12.428718  Lock_time: 0.000302  Rows_sent: 3  Rows_examined: 39211
# Rows_affected: 0  Bytes_sent: 775
# Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes: 1390416
# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
#
# explain: id   select_type table   type    possible_keys   key key_len ref rows    r_rows  filtered    r_filtered  Extra
# explain: 1    SIMPLE  r1  ref userid,gameid,user_game gameid  34  const   258 413.00  100.00  68.04   Using index condition; Using where; Using temporary; Using filesort
# explain: 1    SIMPLE  r2  ref userid,gameid,user_game userid  34  ifdb.r1.userid  20  89.26   100.00  44.06   Using where
# explain: 1    SIMPLE  games   eq_ref  PRIMARY PRIMARY 34  ifdb.r2.gameid  1   1.00    100.00  99.99   Using where
#
SET timestamp=1696915112;
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)

            where
               r1.gameid = 'hsfc7fnl40k4a30q'
               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)

            group by r2.gameid
            order by rand()
            limit 0, 3;