Slow Query: Twine homepage search

[dfabulich]

https://ifdb.org/search?type=game&searchfor=system%3Atwine+-tag%3Asexual%20content+-genre%3Apornographic&sortby=rand

This performs a system:twine -tag:sexual content -genre:pornographic search, order by random. It's doing a full scan of the games table.

select
    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_mv on games.id = gameid

where
    system RLIKE 'twine' AND  NOT (tags rlike '[[:<:]]sexual content[[:>:]]') AND  NOT (genre RLIKE 'pornographic')

order by
    rand()

limit 0, 20;

analyze:

+------+-------------+------------------------+--------+---------------+---------+---------+---------------+-------+----------+----------+------------+----------------------------------------------+
| id   | select_type | table                  | type   | possible_keys | key     | key_len | ref           | rows  | r_rows   | filtered | r_filtered | Extra                                        |
+------+-------------+------------------------+--------+---------------+---------+---------+---------------+-------+----------+----------+------------+----------------------------------------------+
|    1 | SIMPLE      | games                  | ALL    | NULL          | NULL    | NULL    | NULL          | 12987 | 12987.00 |   100.00 |       9.85 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | gameRatingsSandbox0_mv | eq_ref | PRIMARY       | PRIMARY | 34      | ifdb.games.id | 1     | 0.80     |   100.00 |     100.00 |                                              |
+------+-------------+------------------------+--------+---------------+---------+---------+---------------+-------+----------+----------+------------+----------------------------------------------+

[dfabulich]

Ehhh. The slow query log is empty. I'll reopen this if it ever turns up as an actually slow query. (Indexes aren't free!)