Slow Query: "IFDB recommends" `user_proximity` query does a full table scan

[dfabulich]

I'm skeptical that this query works; we might stop doing personalized user_proximity queries entirely.

# Time: 231011 14:54:32
# User@Host: ifdb[ifdb] @ localhost [127.0.0.1]
# Thread_id: 7852209  Schema: ifdb  QC_hit: No
# Query_time: 15.245660  Lock_time: 0.000424  Rows_sent: 12  Rows_examined: 121880
# Rows_affected: 0  Bytes_sent: 13345
# Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes: 3522560
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type table   type    possible_keys   key key_len ref rows    r_rows  filtered    r_filtered  Extra
# explain: 1    SIMPLE  user_proximity  ALL NULL    NULL    NULL    NULL    1660    1765.00 100.00  100.00  Using temporary; Using filesort
# explain: 1    SIMPLE  reviews ref userid,gameid,user_game userid  34  ifdb.user_proximity.userid  20  32.18   100.00  99.86   Using where
# explain: 1    SIMPLE  games   eq_ref  PRIMARY PRIMARY 34  ifdb.reviews.gameid 1   1.00    100.00  99.64   Using where
# explain: 1    SIMPLE  r2  ref userid,gameid,user_game user_game   68  const,ifdb.reviews.gameid   1   0.40    65.73   100.00  Using where; Using index; Not exists
# explain: 1    SIMPLE  pg  ref|filter  userid,gameid   gameid|userid   34|34   ifdb.reviews.gameid 9 (1%)  0.00 (0%)   1.47    100.00  Using where; Not exists; Using rowid filter
# explain: 1    SIMPLE  wl  ref|filter  gameid,userid   gameid|userid   34|34   ifdb.reviews.gameid 9 (0%)  0.02 (0%)   0.03    100.00  Using where; Not exists; Using rowid filter
# explain: 1    SIMPLE  uw  ref|filter  gameid,userid   gameid|userid   34|34   ifdb.reviews.gameid 1 (0%)  0.00 (0%)   0.17    100.00  Using where; Not exists; Using rowid filter
#
SET timestamp=1697036072;
select
                       games.id as gameid,
                       games.title as title,
                       games.author as author,
                       games.`desc` as `desc`,
                       (games.coverart is not null) as hasart,
                       if (sum(646417 - user_proximity.dist) = 0,
                           avg(reviews.rating),
                           sum(reviews.rating
                               *(646417 - user_proximity.dist))
                            / sum(646417 - user_proximity.dist))
                         as avgrating
                     from
                       games
                       join reviews
                       join user_proximity
                       left outer join reviews as r2
                         on games.id = r2.gameid and r2.userid = 'cbwakavqcc79ui27'
                       left outer join playedgames as pg
                         on games.id = pg.gameid and pg.userid = 'cbwakavqcc79ui27'
                       left outer join wishlists as wl
                         on games.id = wl.gameid and wl.userid = 'cbwakavqcc79ui27'
                       left outer join unwishlists as uw
                         on games.id = uw.gameid and uw.userid = 'cbwakavqcc79ui27'
                     where
                       games.id = reviews.gameid
                       and user_proximity.userid = reviews.userid
                       and r2.userid is null
                       and pg.userid is null
                       and wl.userid is null
                       and uw.userid is null
                       and not (reviews.RFlags & 2)
                       and ifnull(now() >= reviews.embargodate, 1)
                       and not (games.flags & 1)
                     group by
                       games.id
                     having
                       count(reviews.rating) >= 5
                     order by
                       avgrating desc
                     limit
                       0, 12;

[dfabulich]

In https://github.com/iftechfoundation/ifdb/pull/274 we disabled this query "temporarily," probably permanently