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

Missing index on series_id for oc_assets_snapshot

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed and reviewed
    • Affects versions: 6.4
    • Fix versions: 7.0
    • Components: Backend Software
    • Labels:
      None
    • Severity:
      Performance
    • Steps to reproduce:
      Hide
      mysql slow query log shows queries like

      SELECT DISTINCT t0.id, t0.archival_date, t0.availability, t0.mediapackage_id, t0.mediapackage_xml, t0.organization_id, t0.owner, t0.series_id, t0.storage_id, t0.version FROM oc_assets_snapshot t0 WHERE ((t0.series_id = '97c93d25-d146-47f4-ac39-37110f193f55') AND (t0.version = (SELECT MAX(t1.version) FROM oc_assets_snapshot t1 WHERE (t1.mediapackage_id = t0.mediapackage_id))));

      As there is no index on the series field, this can scan a lot of rows. Hence:

      alter table oc_assets_snapshot add index IX_oc_assets_snapshot_series (series_id, version);
      Show
      mysql slow query log shows queries like SELECT DISTINCT t0.id, t0.archival_date, t0.availability, t0.mediapackage_id, t0.mediapackage_xml, t0.organization_id, t0.owner, t0.series_id, t0.storage_id, t0.version FROM oc_assets_snapshot t0 WHERE ((t0.series_id = '97c93d25-d146-47f4-ac39-37110f193f55') AND (t0.version = (SELECT MAX(t1.version) FROM oc_assets_snapshot t1 WHERE (t1.mediapackage_id = t0.mediapackage_id)))); As there is no index on the series field, this can scan a lot of rows. Hence: alter table oc_assets_snapshot add index IX_oc_assets_snapshot_series (series_id, version);

      TestRail: Results

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                TestRail: Cases