Change addons_mozilla_org.stats_share_counts indexes


Status Graveyard
4 years ago
2 years ago


(Reporter: sheeri, Assigned: mat)





4 years ago
As per

Here's the query:
# Thread_id: 5044985  Schema: addons_mozilla_org  QC_hit: No
# Query_time: 11.784338  Lock_time: 0.000131  Rows_sent: 0  Rows_examined: 739233
SET timestamp=1376933417;
REPLACE INTO stats_share_counts_totals (addon_id, service, count)
                (SELECT addon_id, service, SUM(count)
                 FROM stats_share_counts
                 RIGHT JOIN addons ON addon_id =
                 WHERE service IN ('digg','facebook','delicious','myspace','friendfeed','twitter')
                 GROUP BY addon_id, service);

After testing, it looks like changing the addons_id index to a composite index, we can get at least 2x performance:

ALTER TABLE stats_share_counts DROP INDEX addon_id, ADD INDEX(addon_id,service,count);

Adding and dropping indexes are online operations; you can confirm this by running the ALTER TABLE first on dev and then stage, before continuing to production.

Comment 1

4 years ago
Please convert this to a migration.
Assignee: server-ops-amo → nobody
Component: Server Operations: AMO Operations → Code Quality
Product: → Marketplace
QA Contact: oremj
Version: other → Avenir


4 years ago
Priority: -- → P3
We no longer have this table in Marketplace. Moving to AMO.
Component: Code Quality → Statistics
Product: Marketplace →
Version: Avenir → unspecified

Comment 3

2 years ago
Mathieu, I think you were looking at indexes so this might be something we should do?
Assignee: nobody → mpillard

Comment 4

2 years ago
Yeah. We still use the same query, the table doesn't have a lot of entries on stage, and I suspect it doesn't have a lot in prod ether, but I think the composite index is a good idea.

Who the hell still uses myspace, friendfeed, delicious or digg though ? We should remove those.

Comment 5

2 years ago
Actually... Nothing writes the share counts anymore, it's been broken since 2014. Plus, back then, the number of shares per day were super low across the whole site, so this functionality really does not make sense anymore IMHO. I'm WONTFIXING this.
Last Resolved: 2 years ago
Resolution: --- → WONTFIX
Product: → Graveyard
You need to log in before you can comment on or make changes to this bug.