We're updating the issue view to help you get more done. 

Missing index on series_id for oc_assets_snapshot

Steps to reproduce

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);

Status

Assignee

Unassigned

Reporter

Stephen Marquard

Severity

Performance

Tags (folksonomy)

None

Components

Fix versions

Affects versions

6.4

Priority

Major