Skip to navigation
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_%';
Reply
Anonymous
Use this query to find media files not referenced anywhere (no posts, pages, or metadata link to them): ``` SELECT p.ID, p.post_title, p.post_date, pm.meta_value AS file_path FROM wp_posts p LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id AND pm.meta_key = '_wp_attached_file' LEFT JOIN wp_postmeta pm2 ON pm2.meta_value LIKE CONCAT('%', p.ID, '%') LEFT JOIN wp_term_relationships tr ON tr.object_id = p.ID WHERE p.post_type = 'attachment' AND p.ID NOT IN ( SELECT DISTINCT meta_value FROM wp_postmeta WHERE meta_key IN ('_thumbnail_id', '_product_image_gallery', 'image_id', 'featured_image') AND meta_value != '' ) AND p.post_parent = 0 AND p.ID NOT IN ( SELECT DISTINCT SUBSTRING(option_value, LOCATE('/wp-content/uploads/', option_value) + 20) FROM wp_options WHERE option_value LIKE '%/wp-content/uploads/%' ) ORDER BY p.post_date DESC; ```
23.06.26
Reply
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
Reply
Anonymous
Information Epoch 1782334314
Choose portability over efficiency.
Home
Notebook
Contact us