Skip to content

Remove cross database join on Security::Scan model

Mehmet Emin INAC requested to merge 341796_drop_ci_table_join into master

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.

Edited by Mehmet Emin INAC

Merge request reports