delete c_price_list,c_lees,c_price,c_propel,c_impel FROM c_price_list LEFT JOIN c_impel ON c_impel.id_price_list=c_price_list .id LEFT JOIN c_lees ON c_lees.id_price_list=c_price_list .id LEFT JOIN c_price ON c_price.id_price_list=c_price_list .id LEFT JOIN c_propel ON c_propel.id_price_list=c_price_list .id WHERE c_price_list.`date`<DATE_SUB(NOW(), INTERVAL 2 DAY)

2 way

 delete from c_impel,c_lees,c_price,c_propel,c_price_list using c_impel,c_lees,c_price,c_price_list,c_propel where c_impel.id_price_list=c_price_list.id and c_lees.id_price_list=c_price_list.id and c_price.id_price_list=c_price_list.id and c_propel.id_price_list=c_price_list.id and c_price_list.`date`<DATE_SUB(NOW(),INTERVAL 2 DAY)

I want to remove all data 2 days ago from the tables. Indices c_price_list.id (Primary key), id_price_list (key). I am waiting for an hour or two, nothing is deleted, the number of rows in the tables is still the same.

  • 1. How many records are deleted at a time, maybe there are a lot of them and should several limited LIMIT queries be performed? 2. If you turn a DELETE query into a SELECT and execute EXPLAIN, is everything normal there? Are all foreign keys indexed? - cheops
  • rather, I wait for an hour, nothing is deleted .- ( - NNN
  • How big are the tables? - cheops
  • prntscr.com/aloeq0 - NNN
  • one
    Most likely because the tables are blocked by the previous DELETE query. Bang it - you still have time to do it. To complete the request, you can use the KILL command, the request PID can be viewed via SHOW PROCESSLIST. And then execute EXPLAIN SELECT. - cheops

1 answer 1

I would delete not from 5 tables at once with one query (I'm not even sure that this is possible), but would execute 5 corresponding deletion requests:

 delete c_propel FROM c_price_list LEFT JOIN c_impel ON c_impel.id_price_list=c_price_list .id LEFT JOIN c_lees ON c_lees.id_price_list=c_price_list .id LEFT JOIN c_price ON c_price.id_price_list=c_price_list .id LEFT JOIN c_propel ON c_propel.id_price_list=c_price_list .id WHERE c_price_list.`date`<DATE_SUB(NOW(), INTERVAL 2 DAY) 
 delete c_price FROM c_price_list LEFT JOIN c_impel ON c_impel.id_price_list=c_price_list .id LEFT JOIN c_lees ON c_lees.id_price_list=c_price_list .id LEFT JOIN c_price ON c_price.id_price_list=c_price_list .id WHERE c_price_list.`date`<DATE_SUB(NOW(), INTERVAL 2 DAY) 
 delete c_lees FROM c_price_list LEFT JOIN c_impel ON c_impel.id_price_list=c_price_list .id LEFT JOIN c_lees ON c_lees.id_price_list=c_price_list .id WHERE c_price_list.`date`<DATE_SUB(NOW(), INTERVAL 2 DAY) 
 delete c_impel FROM c_price_list LEFT JOIN c_impel ON c_impel.id_price_list=c_price_list .id WHERE c_price_list.`date`<DATE_SUB(NOW(), INTERVAL 2 DAY) 
 delete c_price_list WHERE c_price_list.`date`<DATE_SUB(NOW(), INTERVAL 2 DAY)
  • Why you think that it is impossible to delete from 5 tables with one query. . Probably do so. - NNN
  • @RRR, just intuitively, tables are linked, incomprehensible internal order of deletion, somewhere it is possible to get on a missing or locked linked record - ale
  • @RRR, only during the execution of these 5 deletions you need to be sure that from outside these lines nobody changes, adds. I think it's enough to wrap all these requests in one transaction - ale
  • I constantly add records to these labels, can I not delete the early records two days ago (they are never updated, just added)? - NNN
  • one
    This is if you have a field c_price_list. date is the date, if there is also time, then this list (for deletion) will change. In this case, you need to select the row id from c_price_list in the temporary table. And in all removal requests, add an inner join (first after from) with this temporary table by id and in each subsequent condition join replace c_price_list .id with id from this temporary table - ale