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
