This is for people who are good with MySQL queries. I'm using phpMyAdmin locally to try to optimize an old WordPress & Woocommerce site which has gone through a couple of iterations over the years.

I should have ~2000 orphaned image attachments in the wp_posts table and each should have a couple of related entries in the wp_postmeta table. I'm trying to clear them out completely.

I've figure out how to locate these image attachments:

  • they share this in common with ~160 live products: wp_posts.post_type = 'attachment' AND wp_posts.post_parent = 0 AND wp_postmeta.post_id = wp_posts.ID

  • but they do not possess a meta_key = '_thumbnail_id' row in wp_postmeta, only the live products do.

I just don't know how to combine those two statements into an SQL query to find these old products which do not have the _thumbnail_id row. I've tried some combinations of NOT EXISTS to no avail, but I don't know if I'm using it correctly or if I need to use another method.

My approach (maybe you have a better one?) is to locate and delete those products from wp_posts based on these two conditions, and then delete their orphaned metadata from wp_postmeta (which should be easy enough for me).

  • Read up on MySQL LEFT JOIN. You need to join data from 2 separate tables. – WebElaine Oct 25 '17 at 15:17
  • Whenever I find myself thinking how to do things like that with complex SQL, I remind myself that you can write a simple php script and use WP_Query & all the other stuff. Unless you really, really want a pure SQL-version, it's much cleaner to use the WP delete functions (and it's easier, too). – janh Oct 25 '17 at 15:42
  • @WebElaine I've been implementing LEFT JOINs to some extent in other places as I try to optimize this old database, but I haven't been able to figure it out for my above example... i try to use it with NOT EXISTS to compare the post ID across tables but the method only returns a boolean and then either executes the rest of the query or not, which is not really what i intended. – LubosB Oct 25 '17 at 16:09
  • thanks @janh, I may have to look into it if no one provides an answer here. been trying to do it all from phpMyAdmin. – LubosB Oct 25 '17 at 16:10
  • 1
    You might need to run multiple queries. Save the IDs from the first 'share this in common' - then run a second query where ID is in your array of IDs AND your 'do not possess' meta query. – WebElaine Oct 25 '17 at 16:17

Your Answer

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

Browse other questions tagged or ask your own question.