How to get the listing of all the images used in post and pages of a WordPress website to delete the unused images?

Question

I have a website that is over 12 years old and have about 6000 posts and around 100 pages and is using lots of images out there. My media folders is in several GBs. I just want to sort it out so that the unused images can be removed from the media. How can I get simply the listing of images which are used in my website whether in pages or in posts in any manner?

I just want to have a listing of images so that I can reduce my overhead on the server.

I have tried the following query but it's not very fruitful as I want the listing of images so that I can backup/download only those images along with the database.

SELECT `ID`, `post_title`, `guid` FROM `wp_posts` WHERE `post_content` LIKE '%http://example.com/wp-content/uploads/%'

But it shows only the post's title and ID for the above query. I want to retrieve the listing in the format such as:

https://example.com/wp-content/uploads/2010/01/image1.jpg

https://example.com/wp-content/uploads/2010/02/image2.jpg

https://example.com/wp-content/uploads/2010/01/image3.jpg etc. 

Answer 1

You can use a function like REGEXP_SUBSTR to get the URL from the post_content

with cte as (
   select "This is a test http://exampe.com/wp-content/uploads/somethingElse for a test" as post_content
)
select 
   REGEXP_SUBSTR(post_content, 'http://[^ ]*') as url,
   post_content
from cte;

see: DBFIDDLE

NOTE: This simple example does not work correct when there is more than 1 occurrence in the post_content!

Answer 2

1. Go to Media Library or visit /wp-admin/upload.php

2. Select Unattached from the list https://imgur.com/a/FPW62qY

3. Click on Bulk select https://imgur.com/a/UjwsUgV

4. Now click on the first image and then move to the end of all images. Press the shift button and click on the last image. It will select all images at once.

5. Now click on Delete permanently button to delete all unused images.

Note: Please be careful to double-check and take a backup of the full website.