Description
When you pass an addon (and no user) to the ratings API, that results in the following queryset:
Rating.without_replies.all().filter(addon=addon).
Which results in this SQL query:
SELECT `reviews`.`id` FROM `reviews` WHERE (NOT (`reviews`.`deleted` > 0)
AND `reviews`.`reply_to` IS NULL AND `reviews`.`addon_id` = XXX)
ORDER BY `reviews`.`created` DESC
Which looks innocent enough, but the indexes aren't great for that. An explain shows:
Using intersect(reviews_addon_idx,reviews_reply_to_3e3e5a19); Using where; Using filesort.
We should look at the indexes again to see if we can improve that. Maybe simply changing the ORDER BY to order by id instead.
In addition, counting through all those ratings is expensive and shows up often in slow query logs. We maintain a denormalized field (total_ratings) on Addon that we could use when paginating through those results, it could lead to small inconsistencies but I think it's worth it given the performance/scaling benefits.
Acceptance Criteria
┆Issue is synchronized with this Jira Task
Description
When you pass an
addon(and nouser) to the ratings API, that results in the following queryset:Rating.without_replies.all().filter(addon=addon).Which results in this SQL query:
Which looks innocent enough, but the indexes aren't great for that. An explain shows:
Using intersect(reviews_addon_idx,reviews_reply_to_3e3e5a19); Using where; Using filesort.We should look at the indexes again to see if we can improve that. Maybe simply changing the
ORDER BYto order byidinstead.In addition, counting through all those ratings is expensive and shows up often in slow query logs. We maintain a denormalized field (
total_ratings) onAddonthat we could use when paginating through those results, it could lead to small inconsistencies but I think it's worth it given the performance/scaling benefits.Acceptance Criteria
┆Issue is synchronized with this Jira Task