With millions of content keys in the DB, prod selection for the latest audit strategy had started to take 10-50 seconds.
The reason is that we were expecting an index on a content_audit column to find non-existent content_audit rows once we joined the content and content_audit tables to find content with no audits.
What was the fix?
Used an anti-join pattern instead, which can make use of the content_audit::content_key index.
Also limited the content that the query will search to the last 24 hours to further limit the scanning necessary.
Brings latest lookup times in prod with millions of content keys from 10-50 seconds to ~60ms.
What was the problem?
With millions of content keys in the DB, prod selection for the latest audit strategy had started to take 10-50 seconds.
The reason is that we were expecting an index on a
content_auditcolumn to find non-existentcontent_auditrows once we joined thecontentandcontent_audittables to find content with no audits.What was the fix?
Used an anti-join pattern instead, which can make use of the
content_audit::content_keyindex. Also limited the content that the query will search to the last 24 hours to further limit the scanning necessary.Brings latest lookup times in prod with millions of content keys from 10-50 seconds to ~60ms.