ApiQueryArticleFeedback::getUserRatings db queries can be very expensive
Closed, DeclinedPublic

Description

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;
+----+-------------+------------------+-------+----------------------------------+---------+---------+------+---------+-------------+

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

+----+-------------+------------------+-------+----------------------------------+---------+---------+------+---------+-------------+

1SIMPLEarticle_feedbackindexaa_user_page_revision,aa_page_idPRIMARY299NULL9725137Using 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;
+----+-------------+------------------+------+---------------+------------+---------+-------+-------+-----------------------------+

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

+----+-------------+------------------+------+---------------+------------+---------+-------+-------+-----------------------------+

1SIMPLEarticle_feedbackrefaa_page_idaa_page_id4const22936Using 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]

Details

Reference
bz33851
bzimport raised the priority of this task from to Normal.
bzimport set Reference to bz33851.
bzimport added a subscriber: Unknown Object (MLST).

I've added an index (and removed an unused one) in r110967, that would fix this issue, right? I've also added it to the list of schema changes in the Etherpad.

The extension homepage at [[mw:Extension:ArticleFeedback]] says
"This extension has been archived. This extension has not been maintained in some time, and no longer supports recent releases of MediaWiki.
The following other choices are still available: [[mw:Extension:ArticleFeedbackv5]]"

Furthermore, ArticleFeedback (version 4 or earlier) was removed from all Wikimedia wikis in https://gerrit.wikimedia.org/r/#/c/98074/

It is currently unlikely that there will be any further active development of ArticleFeedback (version 4 or earlier).
Closing this report as WONTFIX to reflect reality.
Please feel free to reopen this report in the future if anyone takes the responsibility for active development of ArticleFeedback (version 4 or earlier) again.

Thank you for reporting this bug and we are sorry it could not be fixed.