When your website has been running for a while, it is normal that there are things that are no longer necessary but still exist. One of the most annoying examples is that of images that are no longer used but that continue to take up space in your WordPress.

These images that you no longer use in your posts or pages, or in any other type of content that has become obsolete, remain hosted on your server, wasting resources and storage space. The best thing you can do with them is to eliminate them if you are not going to use them again. But how can you find them and make sure they are not being used anywhere? Let’s see it…

Before Starting

The first thing to do if you want to delete images from your WordPress that are no longer being used is to make a backup copy of your website, including the media files within the folder wp-content.

Although the images you upload to the media library are stored within wp-content/uploads/, it is possible that if you use a plugin to upload the files, they will end up in a different folder. Hence, make sure you have a backup copy of your WordPress database and all the files inside the wp-content folder and not just wp-content/uploads.

Once you have the backup created, we can continue with the following steps…

Detect Orphaned Images in WordPress

There are several ways to detect images that are no longer used in any content within your WordPress website. The most effective is to search them in the WordPress database.

For this we must know that the images you uploaded to the WordPress media library are stored in the table wp_posts as if they were a post or page. What distinguishes them from other contents stored in this table is that they have the attachment value in the post_type column.

Where were we? Oh yeah: we want to find all those images that are not being used in your WordPress. This is easier to define than to do, but don’t worry, we will try to explain it step by step and in a simple way.

All we have to do is write a query in SQL language to run in the WordPress MySQL database. If you don’t know how to access your website database, contact your hosting provider for instructions on how to do it. If they give you access to a phpMyAdmin, it’s also OK ?

The query you need to run is:

SELECT
*
FROM
wp_posts i
WHERE
i.post_type = 'attachment'
AND
NOT EXISTS (SELECT * FROM wp_posts p WHERE p.ID = i.post_parent)
AND
NOT EXISTS (SELECT * FROM wp_postmeta pm WHERE pm.meta_key = '_thumbnail_id' AND pm.meta_value = i.ID)
AND
NOT EXISTS (SELECT * FROM wp_posts p WHERE p.post_type <> 'attachment' AND p.post_content LIKE CONCAT('%',i.guid,'%'))
AND
NOT EXISTS (SELECT * FROM wp_postmeta pm WHERE pm.meta_value LIKE CONCAT('%',i.guid,'%'))

Let’s break it down.

The first thing we do is start with a SELECT statement to select all certain rows of a specific table. In line 2, the star means we want “all columns” in each row.

Lines 3 and 4 indicate the table(s) we’re interested in. In this case, we want to select WordPress attachments which, as I said, are stored in wp_posts, so that’s the table we we’ll use.

From line 5 on, the WHERE statement let us add conditions to filter the rows we want and exclude the rest. Each condition between lines 6 and 14 is separated by the AND instruction, which serves to indicate that all conditions must be met at once. If a row does not meet all WHERE conditions, it will not be returned in the output of the SELECT query.

The first condition in line 6 defines that we want to get only the rows whose value in the post_type column is attachment. As I said before, posts and pages are stored in the wp_posts table too, among other content. With this condition we will only get the images.

For each attachment, WordPress stores whether it was uploaded within the context of a page or post using the column post_parent. Therefore, the condition of line 8 makes us get only those attachments whose post_parent does not exist (that is, they’re not bound to any page or post).

On the other hand, if we choose an image to be the featured image of a post or page, WordPress creates a metadata row in the table wp_postmeta with the key _thumbnail_id and, as a value, it sets the identifier of the attachment image. In line 10 of the query we get those images whose identifier does not appear as the value of a row of wp_postmeta for the key _thumbnail_id.

Line 12 defines a condition to filter images whose URL does not appear in the content of posts or pages. That is, if the URL of the image appears within the content, we assume the image is still useful and therefore should not be deleted. Note that the URL of the image appears in the guid column of the wp_posts table.

Finally, it’s possible that you used some plugin that puts the URL of the images inside rows of the wp_postmeta table. The condition of line 14 allows us to detect this particular case.

And that’s it! Running this SQL query will return all the images that are allegedly not being used anywhere in your site.

Keep in mind that when looking for orphan images, there may be false positives. That is, images that are flagged as unused but are actually being used in WordPress. This is because you may use plugins that define their own tables and keep references to your images there. If this is your case, you must adapt the query by adding additional conditions to cover these cases.

Remove Orphaned Images in WordPress

Now that we have the images that are potentially not being used in WordPress, we should take a look at the results and see if all the images we have listed are being used or not. Depending on what we find, we can refine the query by adding more conditions as we said before. If everything seems correct, you can move on.

To delete an attachment in WordPress we have the function wp_delete_attachment that you can find described here. If you look at the documentation, you will see that this function gets the attachment identifier and a Boolean parameter indicating if you want to force the deletion or just trash it.

When you call this function in WordPress, it will delete the attachment and all its associated information, including possible comments, appearance in taxonomies, related metadata and, most importantly, the image files stored in the wp-content folder.

To get the list of wp_delete_attachment instructions that we will use in PHP, we just have to change line 2 of the previous query so that what the SELECT query returns is the character string that constructs the instruction we want. One for each row returned by the query. You can see the results here:

Now you just have to copy all these instructions and paste them, for example, in the file functions.php of your theme and visit your website. This will cause these instructions to be executed, deleting orphaned images from your WordPress. After that, remove the previous pasted instructions because they will no longer be necessary.

Remember to check that your website is still working perfectly and that you have not deleted relevant images. If something went wrong, don’t worry: you should have a backup of your database and wp-content folder, so you should be able to restore your website to its previous state.