Gilles has submitted this change and it was merged. Change subject: Improve performance for date selection ......................................................................
Improve performance for date selection There's no need to go to the table to generate dates Change-Id: Ia419e0a7fbb0e0af234b5cf33559ee213585025e --- M perf/template.sql 1 file changed, 10 insertions(+), 12 deletions(-) Approvals: Gilles: Verified; Looks good to me, approved diff --git a/perf/template.sql b/perf/template.sql index 93d7a0e..4ff75d5 100644 --- a/perf/template.sql +++ b/perf/template.sql @@ -1,14 +1,12 @@ -SET group_concat_max_len = 10485760; -SELECT * FROM (SELECT concat(substring(timestamp, 1, 4), '-', substring(timestamp, 5, 2), '-', substring(timestamp, 7, 2)) AS datestring FROM ( -SELECT timestamp FROM MultimediaViewerNetworkPerformance_7917896 -WHERE timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 90 DAY)) -UNION ALL -SELECT timestamp FROM MultimediaViewerNetworkPerformance_10596581 -WHERE timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 90 DAY)) -UNION ALL -SELECT timestamp FROM MultimediaViewerNetworkPerformance_10774577 -WHERE timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 90 DAY)) -) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC) dates +SET group_concat_max_len = 18446744073709551615; +SELECT * FROM (SELECT selected_date AS datestring FROM +(SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from + (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, + (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, + (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, + (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, + (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v +WHERE selected_date BETWEEN TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 90 DAY)) AND TIMESTAMP(CURDATE())) dates LEFT OUTER JOIN @@ -39,4 +37,4 @@ UNION ALL SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_10774577 WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 90 DAY)) AND event_total > 20 AND %metricwhere% -) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC ) stats USING (datestring) \ No newline at end of file +) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC ) stats USING (datestring) HAVING imagehit_sample_size IS NOT NULL \ No newline at end of file -- To view, visit https://gerrit.wikimedia.org/r/187170 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: Ia419e0a7fbb0e0af234b5cf33559ee213585025e Gerrit-PatchSet: 1 Gerrit-Project: analytics/multimedia Gerrit-Branch: master Gerrit-Owner: Gilles <gdu...@wikimedia.org> Gerrit-Reviewer: Gilles <gdu...@wikimedia.org> Gerrit-Reviewer: jenkins-bot <> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits