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

Activity

Show:
Stephen Marquard
April 10, 2019, 1:55 PM

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


-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra


-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------

1

PRIMARY

t0

NULL

ALL

NULL

NULL

NULL

NULL

152582

10.00

Using where

2

DEPENDENT SUBQUERY

t1

NULL

ref

UNQ_oc_assets_snapshot,IX_oc_assets_snapshot_mediapackage_id

UNQ_oc_assets_snapshot

386

opencast.t0.mediapackage_id

3

100.00

Using index


-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------
2 rows in set, 2 warnings (0.00 sec)

mysql> alter table oc_assets_snapshot add index IX_oc_assets_snapshot_series (series_id, version);

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


-------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra


-------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------

1

PRIMARY

t0

NULL

ref

IX_oc_assets_snapshot_series

IX_oc_assets_snapshot_series

387

const

1

100.00

Using where

2

DEPENDENT SUBQUERY

t1

NULL

ref

UNQ_oc_assets_snapshot,IX_oc_assets_snapshot_mediapackage_id

UNQ_oc_assets_snapshot

386

opencast.t0.mediapackage_id

3

100.00

Using index


-------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------
2 rows in set, 2 warnings (0.00 sec)

Fixed and reviewed
Your pinned fields
Click on the next to a field label to start pinning.

Assignee

Unassigned

Reporter

Stephen Marquard

Severity

Performance