Gilles has uploaded a new change for review.
https://gerrit.wikimedia.org/r/139780
Change subject: Don't query tables that have only data older than 30 days
......................................................................
Don't query tables that have only data older than 30 days
Change-Id: Idc05daf4db938f7c8f5e63fa47507ca67d80f612
---
M geoperf/template.sql
M perf/template.sql
2 files changed, 0 insertions(+), 21 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/multimedia
refs/changes/80/139780/1
diff --git a/geoperf/template.sql b/geoperf/template.sql
index c6296af..60309d2 100644
--- a/geoperf/template.sql
+++ b/geoperf/template.sql
@@ -19,12 +19,6 @@
GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','),
',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 99th_percentile
FROM (
-SELECT timestamp, event_total, event_country, event_type, wiki FROM
MultimediaViewerNetworkPerformance_7488625
-WHERE %wiki% %metricwhere% AND timestamp < TIMESTAMP(CURDATE()) AND timestamp
>= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 DAY)) AND event_total > 20 AND
event_country != ''
-UNION ALL
SELECT timestamp, event_total, event_country, event_type, wiki FROM
MultimediaViewerNetworkPerformance_7917896
-WHERE %wiki% %metricwhere% AND timestamp < TIMESTAMP(CURDATE()) AND timestamp
>= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 DAY)) AND event_total > 20 AND
event_country != ''
-UNION ALL
-SELECT timestamp, event_total, event_country, event_type, wiki FROM
MultimediaViewerNetworkPerformance_7917896_1
WHERE %wiki% %metricwhere% AND timestamp < TIMESTAMP(CURDATE()) AND timestamp
>= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 DAY)) AND event_total > 20 AND
event_country != ''
) MultimediaViewerNetworkPerformanceUnioned GROUP BY country HAVING
sample_size > 10 ORDER BY mean ASC
\ No newline at end of file
diff --git a/perf/template.sql b/perf/template.sql
index da502a9..54e2ad4 100644
--- a/perf/template.sql
+++ b/perf/template.sql
@@ -1,15 +1,6 @@
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_7393226
-WHERE timestamp < TIMESTAMP(CURDATE()) AND timestamp >=
TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY))
-UNION ALL
-SELECT timestamp FROM MultimediaViewerNetworkPerformance_7488625
-WHERE timestamp < TIMESTAMP(CURDATE()) AND timestamp >=
TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY))
-UNION ALL
SELECT timestamp FROM MultimediaViewerNetworkPerformance_7917896
-WHERE timestamp < TIMESTAMP(CURDATE()) AND timestamp >=
TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY))
-UNION ALL
-SELECT timestamp FROM MultimediaViewerNetworkPerformance_7917896_1
WHERE timestamp < TIMESTAMP(CURDATE()) AND timestamp >=
TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY))
) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY
datestring ASC) dates
@@ -34,12 +25,6 @@
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','),
',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS
%metricname%_time_99th_percentile FROM (
-SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_7488625
-WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >=
TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND event_total > 20 AND
%metricwhere%
-UNION ALL
SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_7917896
-WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >=
TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND event_total > 20 AND
%metricwhere%
-UNION ALL
-SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_7917896_1
WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >=
TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 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
--
To view, visit https://gerrit.wikimedia.org/r/139780
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Idc05daf4db938f7c8f5e63fa47507ca67d80f612
Gerrit-PatchSet: 1
Gerrit-Project: analytics/multimedia
Gerrit-Branch: master
Gerrit-Owner: Gilles <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
