0

I have a wordpress installation where the posts also contain attachments that were not embedded in the content by using the media library -> add.

I would like to find all attachments that are not used in any posts/pages/custom_post_types

It is ok for me if I get results for attachments uploaded for a specific month, for example january 2017.

Here is my attempt to write the SQL:

select distinct a.*
from (
    select *
    from wp_posts
    where post_type = 'attachment'
    and post_date between '2017-01-01' and '2017-01-31'
) as a,
(
    select post_content
    from wp_posts
    where post_type in ('post', 'page', 'custom_post_type_1', 'custom_post_type_2', 'custom_post_type_n')
    and post_status = 'publish'
) as p
where p.post_content not like CONCAT('%/wp-content/uploads/', DATE_FORMAT(a.post_date, '%Y/%m'), '/', a.post_name, '%')

Since not all attachments were embedded in the content via the media library, I cannot use:

select *
from wp_posts
where post_type = 'attachment'
and post_parent = 0;

Does anyone know a better way? My SQL query is quite slow.

  • Have you considered doing this via WP CLI? Doing this via WP CLI or in PHP with WP_Query is trivial. Just keep in mind not every attachment is "attached". If I set my sites header image, that header image won't have a post parent, and would incorrectly show as unused in your query. The same goes for attachments that appear in multiple posts. If the original is deleted, it'll show as unattached despite being used multiple times. Attachments that are uploaded on the post edit screen but never used won't show in this either giving false negatives – Tom J Nowell Apr 14 at 10:15
  • How would you do it with WP_CLI? You are right about the false negatives, that is why I am using this statement in the SQL: where p.post_content not like CONCAT('%/wp-content/uploads/', DATE_FORMAT(a.post_date, '%Y/%m'), '/', a.post_name, '%') – yoho Apr 14 at 10:53
  • That won't catch things like featured images etc which are stored as post IDs. As for WP CLI, the standard post subcommand takes all the same parameters WP_Query does, e.g. --post_type="attachment". If I might ask, why are you querying for unused attachments? – Tom J Nowell Apr 14 at 11:05
  • Ah you are right. I have a Wordpress installation where the editors have uploaded 47.000 images over time while having 3000 posts/pages/custom post types and now they asked me to give them a list of all attachments that are not used anywhere anymore so that they can decide which ones to keep and which ones to delete. Do you have any idea? – yoho Apr 14 at 11:11
  • You'll have to caveat whatever answer you give them by saying there is no 100% foolproof way to do this. There will always be a chance they delete something that's in use. it's also possible they've asked you this because they have their own X Y problem, where they've concealed problem X, and devised a solution, e.g. the site is slow, if we delete attachments we'll speed up the site, lets ask our dev which ones aren't in use. In which case it won't speed up their site – Tom J Nowell Apr 14 at 11:33
0

What you seek isn't truly possible. You can get sort of close, but you cannot definitively say if an attachment is or isn't in use.

We can get somewhere close to a solution, but to do this we will need to process each attachment individually.

So first, get a list of all the IDs of attachments. Use a standard WP_Query to do this, you might even get a speed boost from object cache as a result.

Here's the official date query docs:

https://developer.wordpress.org/reference/classes/wp_query/#date-parameters

For each attachment:

  • Check all post content and post meta for the attachments URL, with the image size removed
  • Check all options and post meta for the attachments ID
  • Check if the attachment has a post parent
  • If any of these are true, the attachment is probably in use

Doing every attachment in a single go will be problematic, it won't be possible to run the query from a browser due to the PHP execution limit. You'd need to do attachments in batches, or via a CLI command. Preferably in batches of 100. PHP will run out of memory if you try to process all 47k at once.

However, the most effective means of testing if an attachment is in use, is to spider the entire site and save the results locally, then search the folder for the URL. Note that this won't catch things that only show once forms submit, or RSS specific stuff, things that only show to logged in users, etc

| improve this answer | |
  • I thought of spidering the website too, so luckily I got the crawl log with all the image urls. What I will do is process all attachments in batches (by 100 or per month) by fetching individual ones and trying to match their URL in the crawl log. It will not be fast, but definitely more accurate. – yoho Apr 14 at 12:05
  • If you separate each check you can do them all in parallel, there's lots of room for speed improvement here, otherwise run it in the background over night – Tom J Nowell Apr 14 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.