How to free up space from the host by deleting attachments that are no longer used
You may find yourself in the situation of having to free up space from your hosting because you have reached the maximum limit of available space and you could solve this problem simply by identifying and deleting all those files that may have remained unused.
We are talking about all those images or photos and all those allegations you may have removed from an article or a page during a review, but which has actually been forgotten about hosting, taking up unnecessary space.
To be able to identify them all, you can enter the phpMyAdmin panel and run this query on the database that your WordPress installation uses.
elect t.id, t.post_title, t.post_name, t.guid percorso
from blog_posts t
where t.post_type = 'attachment'
AND not exists (SELECT 1
FROM blog_posts p
LEFT JOIN blog_postmeta m
ON p.ID = m.post_id
AND m.meta_key = '_thumbnail_id'
WHERE m.meta_value = t.id
)
AND not exists (SELECT 1
FROM blog_posts p
WHERE p.post_content like CONCAT('%', t.guid, '%')
)
AND not exists (SELECT 1
FROM blog_posts p
WHERE p.post_content like CONCAT('%"id":', t.id, '%')
)
The query will list all those files that are no longer used in the covers and contents of pages and articles, giving you the possibility to then remove them, as needed, one by one from the WordPress library.
Care must be taken to change the names of all tables used in the query by replacing the prefix wp_ with the one you chose during the installation of WordPress, if you have not used the default prefix.
Update: The query has been updated, now also compatible for Gutemberg