How To Remove Unused Images From Your Media Library in WordPress

Published in Community.

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.

By the way, important notice: if you do not have knowledge about databases, it is best that you be careful with what you do from now on, keep a backup of the database, and check out this WordPress Codex article where they explain everything in more detail.

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:

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:

SQL instruction to detect unused images and get the PHP code that deletes them in WordPress.
SQL instruction to detect unused images and get the PHP code that deletes them in WordPress.

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.

If you have problems with SQL and the database, or you didn’t understand anything of what I have explained here, first let me know by writing a comment down below. After that, try using a plugin like Media Cleaner. The final result you’ll get must be very similar to what we have seen in this post.

Featured image by Gary Chan on Unsplash.

20 thoughts on “How To Remove Unused Images From Your Media Library in WordPress

  1. I tried to convert the mentioned select query to left join usage and compare server average loading for these two queries.
    Turned out that Antonio’s query (with SELECT) has much less load on server than the query with LEFT JOIN
    Here is mysql monitor of provided SELECT query
    https://paste.pics/00eaa3a95d497ae6d6694871c16fe63d
    and here is the same query with LEFT JOIN used
    https://paste.pics/7ba059a69cd34ff9fb92018189aeb2ba
    As you can see first one has much less “questions” for server, at the same time the first query takes for about 30% more time than the second one

  2. Brilliant, thanks for this really useful guide!

  3. Antonio, thanks a lot. Good article, code works.

    I have 2 questions, I hope you can help me.

    1. Don’t you know how to write SQL query to check pics, which are not only from medigallery, but from all website – from all folders in website file system, where could be loaded pics / files via FTP?

    2. I noticed if I load pic to mediagallery via WP admin panel, and after use this pic just in html, in template, – your code finds this pics as unused. But it is really used. How to write SQL query that such pic wouldn’t be found for deleting?

    1. Unfortunately, if the image is included in HTML files that whose content is not stored in the database, SQL won’t be helpful. You’ll need to search files using regular expressions or something similar.

  4. Thank you a lot.
    I will try to find decision for this case.

  5. Hi, this is a brilliant article for someone like me who is just trying to learn sql. One question: what does the “i” mean in line 4 after wp-posts and again in line 5 as “i.post-type” when the table name is just “post-type”? Is “i” an sql function as I can’t reference it anywhere and you don’t explain its usage.
    Thanks again for the article. Cheers, James

    1. Hi James. The i referes to the table name so that you don’t have to write wp_posts.post_type. In the FROM statement you define the tables you use and can set an identifier to be used as a reference to the table name.

  6. Thanks for such a quick response. Makes perfect sense!
    James

  7. Hi again, Antonio. Okay, so I tried running your sql solution. But the problem is, the client’s database is nearly 1.5GB full of images! That’s why no one else could fix it. I rent a VPS server, and apart from it taking up nearly all the available disk space, it crashes the server when I try to run your sql query because it spikes the CPU, goes over the 100% threshold and crashes the server!
    I’ve tried to set up a localhost on my PC with Laragon, but it times out early in the query. Is there any way to chunk down the sql so that it can be handled a piece at a time? I have to find a way to get rid of thousands of images in his database that aren’t used. I tried selected the ‘unattached’ option in the WordPress Media Library, but it too gives up after a while. He’s relying on me to find a solution, but I’ve run out of possibilities. Any ideas on how to resolve this issue? If anyone can, it’s you!

    1. You could try to do the SQL query on batches. To do so, first execute the following query to know how many attachments are in the database:

      SELECT * FROM wp_posts i WHERE i.post_type = 'attachment'

      With this you should be able to discover the min and max post IDs. Then, execute the original SQL query adding an additional condition to, for example, get the first 100 attachments. Include in the WHERE clause something like i.ID >= 0 AND i.ID < 100. Try that with the number of attachments that make your server work. You'll need to change the interval accordingly. It may take you a while, but at least it should work.

      1. Antonio, you are brilliant! I tried using your batch query and it came back with the last ID set at 288, 753. The first image ID starts somewhere around 300. The problem is, even if I set i.post_type = ‘attachment’ AND i.ID >= 0 AND i.ID < 500 it takes six minutes to get the results, and there is usually only around ten. Anything more, and phpMyAdmin stops working and I get the error: "Error in processing request Error code: 500 Error text: Internal Server Error (rejected) It seems that the connection to server has been lost. Please check your network connectivity and server status."
        That's using my own PC, running Laragon and setting php.ini to have at least 10GB memory. With such a huge number of images, I'd be running functions.php and deleting just a few until the end of next year!
        Do you have any more powerful equipment than I have, and if so, would you be able to sort this out for a fee, and how much would you charge? I'm desperate to get this sorted before Christmas, and the client is wondering if I know what I'm doing as I've pretty much tried eveything without results!
        The only other thing I'm wondering is if I went into File Manager/FTP and renamed all the image upload folders except one, (eg renamed 2016 as old-2016 or something) and then ran your sql query on the one 'live' folder, then went through each one individually, would that lighten the processing load? I just don't know enough about the wp database to know if this would work. Any help you can give me would be much appreciated, and you'd become my 'go to' person for help and support (paid of course). Cheers, James.

        1. Unfortunately, we do not provide consultancy services anymore. Anyway, renaming image folders won’t fix the issue. All the data you need to query is in the database. Just install a MySQL database in your computer, load the database of your client there and with a MySQL client, connect directly. No need to use Laragon or anything else. If the process is slow, you could try to do it incrementally using procedures to automatize it. Also, to reduce the size of the database, remove any other table on your local installation different than wp_posts and wp_postmeta.

  8. It’s a great tutorial!
    But when you use an image that already exists in the media, it doesn’t attach to the page.
    So how do you find that this image is used? I have the impression that the lines where you are using i.guid is not working so since the image is not attached.
    Thank you

    1. We look for its URL in the post_content. The URL (guid) of the image appears in the content if it was included there.

      1. So I do not understand, because there are misses: ‘(
        I have a background sent from an elementor page that attaches well to the page,
        a background selected from this elementor page which is not declared as attached (because as I said, probably already existing) and yet it is in the results of the select with all the unattached

  9. You should get a Noble price for this contribution.

Leave a Reply

Your email address will not be published. Required fields are marked: •

I have read and agree to the Nelio Software Privacy Policy

Your personal data will be located on SiteGround and will be treated by Nelio Software with the sole purpose of publishing this comment here. The legitimation is carried out through your express consent. Contact us to access, rectify, limit, or delete your data.