Here are safe WordPress database cleanup queries (run via phpMyAdmin or WP admin — always backup first):23.06.26

-- 1. Delete post revisions (leave latest)
DELETE FROM wp_posts WHERE post_type = 'revision';

-- 2. Delete auto-drafts
DELETE FROM wp_posts WHERE post_status = 'auto-draft';

-- 3. Delete trashed posts
DELETE FROM wp_posts WHERE post_status = 'trash';

-- 4. Delete spam comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';

-- 5. Delete trashed comments
DELETE FROM wp_comments WHERE comment_approved = 'trash';

-- 6. Delete unapproved comments
DELETE FROM wp_comments WHERE comment_approved = '0';

-- 7. Delete orphaned post meta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

-- 8. Delete orphaned comment meta
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

-- 9. Delete orphaned user meta
DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT ID FROM wp_users);

-- 10. Delete orphaned term meta
DELETE FROM wp_termmeta WHERE term_id NOT IN (SELECT term_id FROM wp_terms);

-- 11. Delete expired transients
DELETE FROM wp_options WHERE option_name LIKE '_transient_%' OR option_name LIKE '_transient_timeout_%';

-- 12. Delete postmeta from revisions (run after #1)
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

-- 13. Clean up unused tags (tags with 0 posts)
SELECT t.*, tt.* FROM wp_terms t
INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
WHERE tt.taxonomy = 'post_tag' AND tt.count = 0;
-- Then delete if the above returns what you expect:
-- DELETE t.*, tt.* FROM wp_terms t
-- INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
-- WHERE tt.taxonomy = 'post_tag' AND tt.count = 0;
Bulk combo (runs all safe deletes in one go):
DELETE FROM wp_posts WHERE post_type IN ('revision', 'auto-draft');
DELETE FROM wp_comments WHERE comment_approved IN ('spam', 'trash');
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON p.ID = pm.post_id WHERE p.ID IS NULL;
DELETE FROM wp_options WHERE option_name LIKE '_transient_%' OR option_name LIKE '_transient_timeout_%';

Anonymous

23.06.26

Anonymous

## Clean Media Script

```
#!/bin/bash
# Safe orphaned media deleter for WordPress
# Usage: bash clean-media.sh          (dry-run, only reports)
#        bash clean-media.sh --delete (actually delete)
# Replace "wp_" prefix below if yours differs

set -euo pipefail
PREFIX="wp_"
DRY_RUN=true
[[ "${1:-}" == "--delete" ]] && DRY_RUN=false

echo "Scanning for orphaned media files..."
echo "Mode: $($DRY_RUN && echo 'DRY RUN (no deletions)' || echo 'LIVE (will delete)')"
echo ""

# Step 1: get candidate IDs
CANDIDATES=$(wp db query "
  SELECT p.ID,
    (SELECT meta_value FROM ${PREFIX}postmeta WHERE post_id=p.ID AND meta_key='_wp_attached_file') AS file
  FROM ${PREFIX}posts p
  WHERE p.post_type='attachment'
    AND p.post_parent=0
    AND p.ID NOT IN (SELECT meta_value FROM ${PREFIX}postmeta WHERE meta_key='_thumbnail_id')
    AND p.ID NOT IN (SELECT meta_value FROM ${PREFIX}postmeta WHERE meta_key='_product_image_gallery')
  ORDER BY p.post_date DESC
" --skip-column-names 2>/dev/null)

if [[ -z "$CANDIDATES" ]]; then
  echo "No orphan candidates found."
  exit 0
fi

TOTAL=0
DELETED=0
SKIPPED=0

echo "$CANDIDATES" | while IFS=$'\t' read id file; do
  [[ -z "$id" ]] && continue
  TOTAL=$((TOTAL + 1))
  FILENAME=$(basename "${file:-unknown}")
  NAME_NO_EXT="${FILENAME%.*}"

# Deep-check: scan all post content, meta, options, term descriptions
  REFS=$(wp db query "
    SELECT (
      (SELECT COUNT(*) FROM ${PREFIX}posts WHERE post_content LIKE '%${NAME_NO_EXT}%' OR post_content LIKE '%wp-image-${id}%' OR post_content LIKE '%attachment_${id}%' OR post_content LIKE '%\"${id}\"%' OR post_content LIKE '%=${id}&%' OR post_content LIKE '%/${id}%') +
      (SELECT COUNT(*) FROM ${PREFIX}postmeta WHERE meta_value LIKE '%${NAME_NO_EXT}%' OR meta_value LIKE '%wp-image-${id}%' OR meta_value LIKE '%attachment_${id}%') +
      (SELECT COUNT(*) FROM ${PREFIX}options WHERE option_value LIKE '%${NAME_NO_EXT}%' OR option_value LIKE '%wp-image-${id}%') +
      (SELECT COUNT(*) FROM ${PREFIX}termmeta WHERE meta_value LIKE '%${NAME_NO_EXT}%') +
      (SELECT COUNT(*) FROM ${PREFIX}term_taxonomy WHERE description LIKE '%${NAME_NO_EXT}%')
    ) AS total
  " --skip-column-names 2>/dev/null)

if [[ "$REFS" -gt 0 ]]; then
    echo "SKIP  ID=$id | $file | still has $REFS reference(s)"
    SKIPPED=$((SKIPPED + 1))
  else
    if $DRY_RUN; then
      echo "DRY   ID=$id | $file | would delete"
    else
      wp media delete "$id" --force 2>/dev/null && echo "DELET ID=$id | $file" || echo "FAIL  ID=$id | $file"
      DELETED=$((DELETED + 1))
    fi
  fi
done

echo ""
echo "Done: $TOTAL candidates | $SKIPPED skipped (still referenced) | $( ($DRY_RUN && echo '0 deleted (dry-run)') || echo "$DELETED deleted")"
```

+ Thought: 329ms
# Validate script syntax
$ bash -n /home/veto/webs/ambrejolie.com/clean-media.sh && echo "SYNTAX OK" || echo "SYNTAX ERROR"
SYNTAX OK
Script created at /home/veto/webs/ambrejolie.com/clean-media.sh
Usage:
bash clean-media.sh — dry-run, only reports orphans
bash clean-media.sh --delete — actually deletes truly orphaned files
The script checks more than just _thumbnail_id — it also scans:
All post content for filename, wp-image-{id}, attachment_{id}, JSON references
All postmeta and termmeta
All options (widgets, settings)
Term descriptions
Only files with zero references anywhere get deleted.

23.06.26

Anonymous

Here are safe WordPress database cleanup queries (run via phpMyAdmin or WP admin — always backup first):23.06.26

Information Epoch 1782334314