add index for querying for the latest valid authz

jmhodges · jmhodges · commit 7de4ffdc369c · 2015-11-05T14:27:55.000-08:00
Previously, when requesting a new certificate, we might churn through many authz attempts to find the latest authz that was valid for a given domain. There was an index on registration id, but a given registration may have very many authzs. Think CDNs with one registration. Updates letsencrypt#959
diff --git a/sa/_db/dbconf.yml b/sa/_db/dbconf.yml
@@ -4,3 +4,7 @@ test:
 integration:
   driver: mysql
   open: root@tcp(localhost:3306)/boulder_sa_integration
+# what goose uses by default, even during migration creation
+development:
+  driver: mysql
+  open: root@tcp(localhost:3306)/boulder_sa_integration
diff --git a/sa/_db/migrations/20151025174914_AddLatestValidAuthzIndex.sql b/sa/_db/migrations/20151025174914_AddLatestValidAuthzIndex.sql
@@ -0,0 +1,11 @@
+
+-- +goose Up
+-- SQL in section 'Up' is executed when this migration is applied
+
+CREATE INDEX `registrationID_identifier_status_expires_authz_idx` on authz (`registrationID`, `identifier`, `status`, `expires` desc);
+
+
+-- +goose Down
+-- SQL section 'Down' is executed when this migration is rolled back
+
+DROP INDEX `registrationID_identifier_status_expires_authz_idx` on `authz`;
