As per https://bugzilla.mozilla.org/show_bug.cgi?id=862889#c14 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 = addons.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.
Please convert this to a migration.
We no longer have this table in Marketplace. Moving to AMO.
Mathieu, I think you were looking at indexes so this might be something we should do?
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.
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.