Awight has uploaded a new change for review.
https://gerrit.wikimedia.org/r/282108
Change subject: Comment about query performance
......................................................................
Comment about query performance
Change-Id: I20f4efe1225d48d6248c5381f2032a8d0dd0a4a3
---
M silverpop_export/update_table.sql
1 file changed, 13 insertions(+), 2 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/08/282108/1
diff --git a/silverpop_export/update_table.sql
b/silverpop_export/update_table.sql
index 244992e..5b62256 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -1,4 +1,6 @@
-- Updates the silverpop_export table
+--
+-- Timing is from a 2016-04-07 production job.
SET autocommit = 1;
@@ -53,6 +55,7 @@
-- Populate, or append to, the storage table all contacts that
-- have an email address. ID is civicrm_email.id.
+-- (15 minutes)
INSERT INTO silverpop_export_staging
(id, contact_id, email, first_name, last_name, preferred_language, opted_out)
SELECT
@@ -71,6 +74,7 @@
-- the latest donation first, with the larger prevailing for an
-- email with multiple simultaneous donations. All the rest for
-- that email will be ignored due to the unique constraint.
+-- (12 minutes)
INSERT IGNORE INTO silverpop_export_latest
SELECT
e.email,
@@ -92,9 +96,10 @@
ct.receive_date DESC,
ct.total_amount DESC;
--- Populate data from contribution tracking; because that's fairly
+-- Populate data from contribution tracking, because that's fairly
-- reliable. Do this before deduplication so we can attempt to make
-- intelligent fallbacks in case of null data
+-- (11 minutes)
UPDATE
silverpop_export_staging ex,
civicrm.civicrm_contribution ct,
@@ -106,6 +111,7 @@
dct.contribution_id = ct.id AND
dct.language IS NOT NULL;
+-- (15 minutes)
UPDATE
silverpop_export_staging ex,
civicrm.civicrm_contribution ct,
@@ -181,6 +187,7 @@
INDEX spexs_email (email)
) COLLATE 'utf8_unicode_ci';
+-- (30 minutes)
INSERT INTO silverpop_export_stat
(email, exid, max_amount_usd, total_usd, cnt_total, has_recurred_donation)
SELECT
@@ -193,6 +200,7 @@
WHERE ct.total_amount IS NOT NULL
GROUP BY e.email;
+-- (10 minutes)
UPDATE silverpop_export_staging ex, silverpop_export_stat exs
SET
ex.highest_usd_amount = exs.max_amount_usd,
@@ -235,7 +243,8 @@
ex.opted_out = 0;
-- And now updated by civicrm address where we have a country but no
--- city from contribution tracking; the countries must match
+-- city from contribution tracking, the countries must match
+-- (11 minutes)
UPDATE silverpop_export_staging ex
JOIN civicrm.civicrm_address addr ON ex.contact_id = addr.contact_id
JOIN civicrm.civicrm_country ctry
@@ -283,6 +292,7 @@
email varchar(255)
);
+-- (20 minutes)
INSERT INTO silverpop_deleted
(email_id, contact_id, email)
SELECT e.id, c.id, e.email
@@ -342,6 +352,7 @@
) COLLATE 'utf8_unicode_ci';
-- Move the data from the staging table into the persistent one
+-- (12 minutes)
INSERT INTO silverpop_export (
id,contact_id,first_name,last_name,preferred_language,email,opted_out,
has_recurred_donation,highest_usd_amount,lifetime_usd_total,donation_count,
--
To view, visit https://gerrit.wikimedia.org/r/282108
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I20f4efe1225d48d6248c5381f2032a8d0dd0a4a3
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Awight <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
