MySQL Index on query with Where, Group By and OrderBy clauses

How can i improve the performance of below query? What indexes might help?

SELECT platform, country, Source, window,
Round(SUM(ProjectedARPI*PlayerCount) / SUM(PlayerCount), 2) AS ProjectedARPI,
Round(SUM(ProjectedARPIOrganicLow*PlayerCount) / SUM(PlayerCount), 2) AS ProjectedARPIOrganicLow,
Round(SUM(ProjectedARPIOrganicMed*PlayerCount) / SUM(PlayerCount), 2) AS ProjectedARPIOrganicMed,
Round(SUM(ProjectedARPIOrganicHigh*PlayerCount) / SUM(PlayerCount), 2) AS ProjectedARPIOrganicHigh,
SUM(PlayerCount) AS PlayerCount, SUM(PayerCount) AS PayerCount,
CASE WHEN(SUM(PlayerCount) > 500 AND SUM(PayerCount) > 10) THEN TRUE ELSE FALSE END AS isSignificant,
min(CohortRangeLow) as CohortRangeLow,
max(CohortRangeHigh) as CohortRangeHigh
FROM web_synch.UI_data
WHERE PlayerCount > 0 AND ProjectionDate BETWEEN '2015-07-25' AND '2016-10-25' AND window = 365
GROUP BY Platform, country, source, ProjectionDate
ORDER BY Platform, source, ProjectionDate;

For this query, basically your only hope in using indexes is either: UI_data(window, ProjectionDate, PlayerCount)
or UI_data(window, PlayerCount, ProjectionDate)
. Which is better depends on which selects fewer records . . . I would guess the first is better.

Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » MySQL Index on query with Where, Group By and OrderBy clauses

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录