Author: afeldman
Description:
I noticed the following query running on an enwiki slave for 10 minutes, before I killed it. See in the explain:
mysql> explain SELECT /* ApiQueryArticleFeedback::getUserRatings 142.155.15.205 */ aa_rating_id,aa_revision,aa_rating_value FROM article_feedback WHERE aa_page_id = '30902154' AND aa_rating_id IN ('1','2','3','4') AND aa_user_id = '0' AND aa_user_text = '142.155.15.205' AND aa_user_anon_token = 'WA5aPep9ImRrfAO4iKJiQkckv1O6fcDG' ORDER BY aa_revision DESC LIMIT 4;
+----+-------------+------------------+-------+----------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+----------------------------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | article_feedback | index | aa_user_page_revision,aa_page_id | PRIMARY | 299 | NULL | 9725137 | Using where |
+----+-------------+------------------+-------+----------------------------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)
It would probably be nice if there was an index on aa_user_text, but this query specifies a single aa_page_id and forcing that drops the examined rows lots:
mysql> explain SELECT /* ApiQueryArticleFeedback::getUserRatings 142.155.15.205 */ aa_rating_id,aa_revision,aa_rating_value FROM article_feedback USE INDEX (aa_page_id) WHERE aa_page_id = '30902154' AND aa_rating_id IN ('1','2','3','4') AND aa_user_id = '0' AND aa_user_text = '142.155.15.205' AND aa_user_anon_token = 'WA5aPep9ImRrfAO4iKJiQkckv1O6fcDG' ORDER BY aa_revision DESC LIMIT 4;
+----+-------------+------------------+------+---------------+------------+---------+-------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+------------+---------+-------+-------+-----------------------------+
| 1 | SIMPLE | article_feedback | ref | aa_page_id | aa_page_id | 4 | const | 22936 | Using where; Using filesort |
+----+-------------+------------------+------+---------------+------------+---------+-------+-------+-----------------------------+
1 row in set (0.01 sec)
and the actual query run:
mysql> SELECT /* ApiQueryArticleFeedback::getUserRatings 142.155.15.205 */ aa_rating_id,aa_revision,aa_rating_value FROM article_feedback USE INDEX (aa_page_id) WHERE aa_page_id = '30902154' AND aa_rating_id IN ('1','2','3','4') AND aa_user_id = '0' AND aa_user_text = '142.155.15.205' AND aa_user_anon_token = 'WA5aPep9ImRrfAO4iKJiQkckv1O6fcDG' ORDER BY aa_revision DESC LIMIT 4;
Empty set (1.04 sec)
Version: unspecified
Severity: enhancement
Whiteboard: wikimedia[unmaintained]