Last week we deployed a change which caused sa.GetRevocationStatus to use a database query (SELECT * from revokedCertificates WHERE serial = ?) for which the table didn't have an index. This worked fine in CI, dev, and staging, where that table is tiny. It caused serious performance problems in prod, where each instance of that query required a full table scan.

MariaDB's Slow Query Log has a log_queries_not_using_indexes flag. When enabled, the database will log queries like the one which caused us problems.

We should find a way to write an integration test which enables that flag, waits for a significant number of ACME transactions to happen, and then examines the slow query log for any unexpected queries. We'll probably need to configure the flag globally, so it can catch queries from any of the integration tests. We'll probably also need to configure the slow query log very carefully, to ensure it doesn't log anything except these no-index queries, to avoid false positives. Finally, this integration test might have to be very weirdly shaped, to ensure it runs after all the other integration tests have completed.