PERF: add missing bounce_key index to email_logs

PERF: add missing bounce_key index to email_logs

We perform lookups based off bounce_key when emails bounce, we need the index.

diff --git a/db/migrate/20190502223613_add_bounce_key_index_on_email_logs.rb b/db/migrate/20190502223613_add_bounce_key_index_on_email_logs.rb
new file mode 100644
index 0000000..f7d8493
--- /dev/null
+++ b/db/migrate/20190502223613_add_bounce_key_index_on_email_logs.rb
@@ -0,0 +1,14 @@
+class AddBounceKeyIndexOnEmailLogs < ActiveRecord::Migration[5.2]
+  def change
+    execute <<~SQL
+      DELETE FROM email_logs l
+      WHERE bounce_key IS NOT NULL
+        AND id > (
+          SELECT MIN(id)
+          FROM email_logs l2
+          WHERE l2.bounce_key = l.bounce_key
+        )
+    SQL
+    add_index :email_logs, [:bounce_key], unique: true, where: 'bounce_key IS NOT NULL'
+  end
+end

GitHub sha: bb8cdf9e

1 Like

I think we need to add the index before running the DELETE query. The delete query scans through bounce_key and without the index it is taking forever for me locally.

1 Like

I’ll just assume no dupes then, this is simpler and odds of dupes here are pretty much zero.

O right it is an unique index so we can’t add it before the delete query.

DEV: follow up on bb8cdf9e, assume no dupe bounce_keys