Here's an example slow query from the "Authored Games" section of the user profile page. As I read it, it's doing a full scan of the entire reviews table.
See also #401
# User@Host: ifdb[ifdb] @ localhost [127.0.0.1]
# Thread_id: 6588583 Schema: ifdb QC_hit: No
# Query_time: 19.694899 Lock_time: 0.000966 Rows_sent: 1 Rows_examined: 215063
# Rows_affected: 0 Bytes_sent: 1195
# Tmp_tables: 5 Tmp_disk_tables: 1 Tmp_table_sizes: 7012300
# 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 gameprofilelinks ref gameid,userid userid 34 const 1 1.00 100.00 100.00 Using index condition; Using temporary; Using filesort
# explain: 1 PRIMARY games eq_ref PRIMARY PRIMARY 34 ifdb.gameprofilelinks.gameid 1 1.00 100.00 100.00
# explain: 1 PRIMARY <derived4> ref key0 key0 34 ifdb.gameprofilelinks.gameid 10 1.00 100.00 100.00
# explain: 4 DERIVED <derived5> ALL NULL NULL NULL NULL 68110 22599.00 100.00 100.00 Using temporary; Using filesort
# explain: 5 DERIVED reviews ALL NULL NULL NULL NULL 68110 68110.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=1696913654;
select
games.id as id, games.title as title, games.author as author,
date_format(games.published, '%Y') as year,
games.`desc` as `desc`,
(coverart is not null) as hasart,
grv.avgRating as rating,
grv.numRatingsInAvg as ratingCnt,
flags
from
games
join gameprofilelinks
on games.id = gameprofilelinks.gameid
left outer join gameRatingsSandbox0 as grv
on grv.gameid = games.id
where
gameprofilelinks.userid = 'wr91n7k4p0ohlxb'
group by games.id
order by starsort desc;
Here's an example slow query from the "Authored Games" section of the user profile page. As I read it, it's doing a full scan of the entire
reviews
table.See also #401