Slow library performance due to increasingly growing database

Plex Media Server
28

Might not be the best SQL queries but for reference I used these, with regards to:

  1. Delete all entries in metadata_items that relate to a metadata_item_id which has too many extras (find Volts handy query above)
  2. Delete all orphaned media_streams entries (when a media stream is for a media item which relates to a metadata item that doesn’t exist)
  3. Delete all orphaned media_parts entries (when a media part is for a media item which relates to a metadata item that doesn’t exist)
  4. Delete all orphaned media_items entries (when a media item entry is for a metadata item that doesn’t exist)
  1. Since I only had 3 offending IDs I did these manually, you’d need to write some other command to delete all matches.
DELETE FROM metadata_relations WHERE metadata_item_id = '<offending-id>'

DELETE FROM metadata_items
WHERE id IN (
    SELECT related_metadata_item_id 
    FROM metadata_relations 
    WHERE metadata_item_id = '<offending-id>'
)

DELETE FROM media_streams
WHERE media_item_id IN (
    SELECT media_streams.media_item_id 
    FROM media_streams
    INNER JOIN media_items ON media_streams.media_item_id=media_items.id
    WHERE media_items.metadata_item_id NOT IN (
        SELECT id 
        FROM metadata_items
    )
)

DELETE FROM media_parts 
WHERE media_item_id in (
    SELECT media_parts.media_item_id 
    FROM media_parts INNER JOIN media_items ON media_parts.media_item_id=media_items.id
    WHERE media_items.metadata_item_id NOT IN (
        SELECT id
        FROM metadata_items
    )
)

DELETE FROM media_items
WHERE metadata_item_id NOT IN (
    SELECT id
    FROM metadata_items
)
3 Likes