Absolute beginner, trying to LIMIT & ORDER a DELETE/JOIN query in MySQL

Question

I've been handed a MySQL query for deleting millions of entires that I'm trying to add a LIMIT to, which also ORDERS BY ASC. I've little idea what I'm doing, and I'm very stuck.

From what I understand you can't LIMIT a multi-table DELETE, but the solutions I've found that circumvent this issue have been too complicated for me to adapt on my own.

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE d.taxonomy = 'edd_log_type' AND a.post_type = 'edd_log' AND e.term_id = 167
ORDER BY ID ASC LIMIT 100;

Expectation: I'm trying to construct a query that will find and delete the oldest 100 entries. I'm placing a LIMIT because the amount of entries (millions) being worked with time out the query otherwise.

Actual result: syntax errors due to LIMIT not working with multi-table deletes, as far as I understand at least.

Answer 1

You should not use columns related to left joined table in where clause because using where this work as inner join In these cases you should move the related condition in the ON clause

And for deletion (based on your code) you could join you delete statemente with a query that return the 100 id you need

  delete a,b,c 
  FROM wp_posts a
  LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
  LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
  LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) AND d.taxonomy = 'edd_log_type'
  LEFT JOIN wp_terms e ON ( e.term_id = d.term_id ) AND e.term_id = 167
  INNER JOIN  ( 

      select a.id 
      FROM wp_posts a
      LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
      LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
      LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) AND d.taxonomy = 'edd_log_type'
      LEFT JOIN wp_terms e ON ( e.term_id = d.term_id ) AND e.term_id = 167
      WHERE a.post_type = 'edd_log'
      ORDER BY ID ASC 
      LIMIT 100

  ) t ON t.id = a.id 
  WHERE AND a.post_type = 'edd_log'

and as suggested by Strawberry for a delete is better the use of inner join (you can't delete the rows that don't match )

  delete a,b,c 
  FROM wp_posts a
  INNER  JOIN wp_term_relationships b ON ( a.ID = b.object_id )
  INNER  JOIN wp_postmeta c ON ( a.ID = c.post_id )
  INNER  JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) AND d.taxonomy = 'edd_log_type'
  INNER  JOIN wp_terms e ON ( e.term_id = d.term_id ) AND e.term_id = 167
  INNER JOIN  ( 

      select a.id 
      FROM wp_posts a
      INNER  JOIN wp_term_relationships b ON ( a.ID = b.object_id )
      INNER  JOIN wp_postmeta c ON ( a.ID = c.post_id )
      INNER  JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) AND d.taxonomy = 'edd_log_type'
      INNER  JOIN wp_terms e ON ( e.term_id = d.term_id ) AND e.term_id = 167
      WHERE a.post_type = 'edd_log'
      ORDER BY ID ASC 
      LIMIT 100

  ) t ON t.id = a.id 
  WHERE AND a.post_type = 'edd_log'