Slow Query: search by tags does a full scan of `games` table

[dfabulich]

When searching by tags e.g. https://ifdb.org/search?searchbar=tag%3Awalkthrough it does a full table scan of the games table.

I bet there are a bunch of cleverer ways to handle this.

• Some kinda full-text search index?
• At a minimum we could query the gametags table first and then join to games
• Some punctuation convention allowing us to insist on an exact-match tag search (since all tag links on all games just link to https://ifdb.org/search?searchfor=tag:walkthrough)

# Time: 231011 20:12:16
# User@Host: ifdb[ifdb] @ localhost [127.0.0.1]
# Thread_id: 8085360  Schema: ifdb  QC_hit: No
# Query_time: 11.467029  Lock_time: 0.000919  Rows_sent: 20  Rows_examined: 233154
# Rows_affected: 0  Bytes_sent: 7859
# Tmp_tables: 5  Tmp_disk_tables: 1  Tmp_table_sizes: 7728812
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 1  Priority_queue: No
#
# explain: id   select_type table   type    possible_keys   key key_len ref rows    r_rows  filtered    r_filtered  Extra
# explain: 1    PRIMARY games   ALL NULL    NULL    NULL    NULL    12810   12810.00    100.00  13.83   Using where; Using temporary; Using filesort
# explain: 1    PRIMARY <derived4>  ref key0    key0    34  ifdb.games.id   10  0.94    100.00  100.00
# explain: 4    DERIVED <derived5>  ALL NULL    NULL    NULL    NULL    68129   22611.00    100.00  100.00  Using temporary; Using filesort
# explain: 5    DERIVED reviews ALL NULL    NULL    NULL    NULL    68129   68129.00    100.00  93.12   Using where; Using temporary; Using filesort
# explain: 5    DERIVED users   eq_ref  PRIMARY PRIMARY 34  ifdb.reviews.userid 1   1.00    100.00  100.00  Using where
#
SET timestamp=1697055136;
select sql_calc_found_rows
              games.id as id,
                       games.title as title,
                       games.author as author,
                       games.tags as tags,
                       games.moddate as moddate,
                       games.system as devsys,
                       if (time(games.published) = '00:00:00',
                           date_format(games.published, '%Y'),
                           date_format(games.published, '%M %e, %Y'))
                         as pubfmt,
                       if (time(games.published) = '00:00:00',
                           date_format(games.published, '%Y'),
                           date_format(games.published, '%Y-%m-%d'))
                         as published,
                       date_format(games.published, '%Y') as pubyear,
                       (games.coverart is not null) as hasart,
                       avgRating as avgrating,
                       numRatingsInAvg as ratingcnt,
                       stdDevRating as ratingdev,
                       numRatingsTotal,
                       numMemberReviews,
                       games.sort_title as sort_title,
                       games.sort_author as sort_author,
                       ifnull(games.published, '9999-12-31') as sort_pub,
                       games.flags

            from
              games
                      left join gameRatingsSandbox0 on games.id = gameid
            where
              tags rlike '[[:<:]]walkthrough[[:>:]]'

            group by games.id

            order by
              games.created,
              sort_title
            limit 1620, 20;