Slow Query: Frequent fiction score

The frequent fiction score does a full table scan of the userScores view.

Ideas here include:

Add an index to the view
Most users have posted no reviews, so we can skip the query entirely

# User@Host: ifdb[ifdb] @ localhost [127.0.0.1]
# Thread_id: 6589011  Schema: ifdb  QC_hit: No
# Query_time: 54.337300  Lock_time: 18.378918  Rows_sent: 1  Rows_examined: 71665
# Rows_affected: 0  Bytes_sent: 62
# Tmp_tables: 6  Tmp_disk_tables: 0  Tmp_table_sizes: 12323512
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# 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    PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    323236  1405.00 100.00  100.00  Using where
# explain: 2    DERIVED <derived3>  ALL NULL    NULL    NULL    NULL    323236  65113.00    100.00  100.00  Using temporary; Using filesort
# explain: 3    DERIVED reviews ALL NULL    NULL    NULL    NULL    68110   68110.00    100.00  99.96   Using where; Using temporary; Using filesort
# explain: 3    DERIVED reviewvotes ref reviewid    reviewid    8   ifdb.reviews.id 4   0.67    100.00  100.00  Using where
# explain: 3    DERIVED users   eq_ref  PRIMARY PRIMARY 34  ifdb.reviewvotes.userid 1   0.45    100.00  100.00  Using where
# explain: 3    DERIVED specialreviewers    eq_ref  PRIMARY PRIMARY 4   ifdb.reviews.special    1   0.05    100.00  100.00  Using where
# explain: 4    UNION   reviewvotes ALL NULL    NULL    NULL    NULL    45549   45549.00    100.00  100.00  Using temporary; Using filesort
# explain: 5    UNION   l   ALL NULL    NULL    NULL    NULL    477 477.00  100.00  100.00  Using temporary; Using filesort
# explain: 5    UNION   i   ref listid  listid  34  ifdb.l.id   11  11.37   100.00  100.00  
#
SET timestamp=1696913707;
select count(userid) as rank
         from userScores
         where rankingScore > 0;

iftechfoundation / ifdb-suggestion-tracker

Slow Query: Frequent fiction score #403