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.