https://bugzilla.wikimedia.org/show_bug.cgi?id=33851
Web browser: ---
Bug #: 33851
Summary: ApiQueryArticleFeedback::getUserRatings db queries can
be very expensive
Product: MediaWiki extensions
Version: any
Platform: All
OS/Version: All
Status: NEW
Severity: normal
Priority: Unprioritized
Component: ArticleFeedback
AssignedTo: [email protected]
ReportedBy: [email protected]
CC: [email protected]
Classification: Unclassified
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)
--
Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
You are on the CC list for the bug.
_______________________________________________
Wikibugs-l mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
