525493 - Review count script should be more efficient

Status: RESOLVED WORKSFORME

(addons.mozilla.org Graveyard :: Maintenance Scripts, enhancement, P5)

Description

[Dave Dash [:davedash, :dd] (assign all bugs to mbrandt)]

PHP Notice:  MySQL Error 0: 
Query was: [
           UPDATE addons AS a
           INNER JOIN (
               SELECT
                   versions.addon_id as addon_id,
                   COUNT(*) as count
               FROM reviews
               INNER JOIN versions ON reviews.version_id = versions.id
               WHERE reviews.reply_to IS NULL
                   AND reviews.rating > 0
               GROUP BY versions.addon_id
           ) AS c ON (a.id = c.addon_id)
           SET a.totalreviews = c.count
       ] in /data/amo/www/addons.mozilla.org-remora/bin/database.class.php on line 130


This query is expensive.  It has to go through each addon, get an aggregate and do a join on multiple tables.

For AMO v4, we can just increment the review count, on save of a review - or load this into gearman and do it per addon as needed.

Comment 1

[Dave Dash [:davedash, :dd] (assign all bugs to mbrandt)]

Well AMO is still running so I am going to assume this isn't a real issue.