I have been seeing a lot of slow queries like this:
wikiadmin@10.64.16.175(commonswiki)> explain SELECT /*! STRAIGHT_JOIN */ img_name,img_timestamp,actor_user,actor_name FROM `image` JOIN `actor` ON ((actor_id=img_actor)) LEFT JOIN `user_groups` ON (ug_group = 'bot' AND (ug_user = actor_user) AND (ug_expiry IS NULL OR ug_expiry >= '20211214173620')) WHERE actor_name = 'Gov.mm' AND (ug_group IS NULL) AND (((img_timestamp<'20211214000000'))) ORDER BY img_timestamp DESC LIMIT 51 ; +------+-------------+-------------+--------+-----------------------------------+---------------+---------+------------------------------------+----------+-------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------+--------+-----------------------------------+---------------+---------+------------------------------------+----------+-------------------------+ | 1 | SIMPLE | image | range | img_timestamp,img_actor_timestamp | img_timestamp | 14 | NULL | 29263285 | Using where | | 1 | SIMPLE | actor | const | PRIMARY,actor_name | actor_name | 257 | const | 1 | Using where | | 1 | SIMPLE | user_groups | eq_ref | PRIMARY,ug_group,ug_expiry | PRIMARY | 261 | commonswiki.actor.actor_user,const | 1 | Using where; Not exists | +------+-------------+-------------+--------+-----------------------------------+---------------+---------+------------------------------------+----------+-------------------------+ 3 rows in set (0.004 sec)
The straight join is actually problematic, if you remove it, MySQL realizes the user doesn't exists and return with empty result right away:
wikiadmin@10.64.16.175(commonswiki)> explain SELECT img_name,img_timestamp,actor_user,actor_name FROM `image` JOIN `actor` ON ((actor_id=img_actor)) LEFT JOIN `user_groups` ON (ug_group = 'bot' AND (ug_user = actor_user) AND (ug_expiry IS NULL OR ug_expiry >= '20211214173620')) WHERE actor_name = 'Gov.mm' AND (ug_group IS NULL) AND (((img_timestamp<'20211214000000'))) ORDER BY img_timestamp DESC LIMIT 51 ; +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.001 sec) wikiadmin@10.64.16.175(commonswiki)> SELECT img_name,img_timestamp,actor_user,actor_name FROM `image` JOIN `actor` ON ((actor_id=img_actor)) LEFT JOIN `user_groups` ON (ug_group = 'bot' AND (ug_user = actor_user) AND (ug_expiry IS NULL OR ug_expiry >= '20211214173620')) WHERE actor_name = 'Gov.mm' AND (ug_group IS NULL) AND (((img_timestamp<'20211214000000'))) ORDER BY img_timestamp DESC LIMIT 51 ; Empty set (0.001 sec)
You can replace "Gov.mm" with any gibberish and it would still goes in direction of reading 29M rows. Suggestion: Just run a check if the actor exists before making the query.