Uploaded image for project: 'Opencast'
  1. MH-13380

Add index on snapshot_id to oc_assets_asset

    Details

    • Type: Task
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed and reviewed
    • Affects versions: 6.1
    • Fix versions: 7.0
    • Components: Backend Software

      Description

      Queries like this are relatively slow:

      SELECT t0.id, t0.checksum, t0.mediapackage_element_id, t0.mime_type, t0.size, t0.snapshot_id, t0.storage_id, t1.availability, t1.organization_id FROM oc_assets_asset t0 LEFT OUTER JOIN oc_assets_snapshot t1 ON (t1.id = t0.snapshot_id) WHERE (((t1.mediapackage_id = '1f089f45-1790-441d-a96e-322566c20033') AND (t0.mediapackage_element_id = 'security-policy-series')) AND (t1.version = 3)) ORDER BY t1.version DESC;

      because of a missing index on snapshot_id. To fix:

      CREATE INDEX IX_oc_assets_asset_snapshot_id ON oc_assets_asset (snapshot_id);

        TestRail: Results

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                smarquard Stephen Marquard
              • Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  TestRail: Cases