Remove cross database join on Security::Scan model
What does this MR do and why?
This MR removes a cross-database join.
Related to #341796 (closed).
Database review
This MR renames the model scope of the Security::Scan
called latest_successful_by_build
to latest_successful
along with removing the cross-database join.
The scope is mainly used with pipeline filter and we already have an index on the pipeline_id
column of the security_scans
table called index_security_scans_on_pipeline_id
. I didn't introduce a compound index on (pipeline_id, status, latest)
as the number of security_scans
records per pipeline is not expected to be more than hundreds but can add it based on the feedback from the database maintainer.
Old Query
SELECT
"security_scans".*
FROM
"security_scans"
INNER JOIN "ci_builds" ON "ci_builds"."id" = "security_scans"."build_id"
AND "ci_builds"."type" = 'Ci::Build'
WHERE ("ci_builds"."retried" = FALSE
OR "ci_builds"."retried" IS NULL)
AND "ci_builds"."status" = 'success'
New Query
SELECT
"security_scans".*
FROM
"security_scans"
WHERE
"security_scans"."latest" = TRUE
AND "security_scans"."status" = 1
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.