Query to get feedback for watchlisted pages performs poorly
Closed, DeclinedPublic

Description

Author: afeldman

Description:
This query is regularly taking up to 8 seconds on enwiki and will worsen over time in current form. The ORDER BY is satisfied by the af_relevance_sort_af_id index on aft_article_feedback, but that does nothing for any of the WHERE constraints on that table. af_page_id would be the most reasonable to include in an index that can satisfy the ORDER BY. That should help today but over time, a large number of rows will still have to be scanned for popular articles, so this needs a better long term solution. A search engine would handle this much better.

SELECT /* ArticleFeedbackv5Fetch::run */ af_id, af_net_helpfulness, af_relevance_sort, rating.aa_response_boolean AS yes_no FROM aft_article_feedback LEFT JOIN aft_article_answer rating ON ((rating.aa_feedback_id = af_id) AND rating.aa_field_id IN ('-1', '1', '16') ) LEFT JOIN aft_article_answer comment ON ((comment.aa_feedback_id = af_id) AND comment.aa_field_id IN ('-1', '2', '17') ) WHERE (af_is_deleted IS FALSE) AND (af_is_hidden IS FALSE) AND ((af_is_featured IS TRUE OR af_has_comment is true OR af_net_helpfulness > 0) AND af_relevance_score > -5) AND af_page_id = '5043734' AND (( af_form_id = 1 OR af_form_id = 6 )) ORDER BY af_relevance_sort ASC, af_id ASC LIMIT 51


Version: unspecified
Severity: normal

Details

Reference
bz39326
bzimport raised the priority of this task from to Lowest.
bzimport set Reference to bz39326.
bzimport added a subscriber: Unknown Object (MLST).
bzimport created this task.Aug 14 2012, 6:07 AM

Schema and indexes have changed and this should (for regular central/article feedback pages) be taken care of.

There's still a problem for feedback on watchlisted pages though. There are indexes with aft_page (the suggested af_page_id's equivalent in new schema), but the amount of watchlisted pages for some users is so high, it does not help much.

  • I've changed the bug topic to more accurately reflex this watchlist issue.
  • I've lowered the bug importance/severity. It _is_ a rather big deal, but not critical for now, as the watchlist has been disabled until we can come up with a solution.

Innocenti.Maresin wrote:

I guess, they’ll never come up with a solution. Special:ArticleFeedbackv5Watchlist was one of two things making the extension helpful for experts, the other being the “thumb down” button. The Foundation is not interested in extensions helpful for experts. It is interested in extensions helpful for crowds and the metapedianist mob.

[Lowering priority to reflect reality, as AFTv5 is not very actively being worked on anymore.]

Jdforrester-WMF closed this task as Declined.Mar 5 2015, 10:03 PM
Jdforrester-WMF added a subscriber: Jdforrester-WMF.

All development work on AbuseFilter v.5 (and indeed, previous versions) is halted. The project is archived, so having open tasks is inappropriate. Consequently, I'm closing all tasks.