Slow query: Browse view does a full table scan

[dfabulich]

I think we need to set some kind of index on the starsort column of the gamesRatingsSandbox0 view.

(Also note that large limit, which is likely slowing things down. Maybe we should put a limit on the number of pages we're willing to return…?)

# User@Host: ifdb[ifdb] @ localhost [127.0.0.1]
# Thread_id: 1055040  Schema: ifdb  QC_hit: No
# Query_time: 20.978205  Lock_time: 0.000745  Rows_sent: 20  Rows_examined: 248647
# Rows_affected: 0  Bytes_sent: 7034
# Tmp_tables: 5  Tmp_disk_tables: 1  Tmp_table_sizes: 11563516
# 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    12732   12732.00    100.00  100.00  Using temporary; Using filesort
# explain: 1    PRIMARY <derived4>  ref key0    key0    34  ifdb.games.id   10  0.61    100.00  100.00  
# explain: 4    DERIVED <derived5>  ALL NULL    NULL    NULL    NULL    67604   22494.00    100.00  100.00  Using temporary; Using filesort
# explain: 5    DERIVED reviews ALL NULL    NULL    NULL    NULL    67604   67604.00    100.00  93.11   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=1696118423;
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
              1

            group by games.id
            having avgRating >= '4'
            order by
              ratingcnt desc, starsort desc,
              sort_title
            limit 640, 20;

[dfabulich]

We're using a materialized view now.