0

I have two tables, first table (table1):

| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | bigint(20) | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11)    | NO   | MUL | NULL    |                |
| who_id   | int(11)    | NO   | MUL | NULL    |                |
| ts       | int(11)    | NO   | MUL | NULL    |                |
+----------+------------+------+-----+---------+----------------+

second table (table2):

+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | bigint(20) | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11)    | NO   | MUL | NULL    |                |
| owner_id | int(11)    | NO   | MUL | NULL    |                |
| ts       | int(11)    | NO   |     | NULL    |                |
| was_read | tinyint(1) | NO   |     | NULL    |                |
+----------+------------+------+-----+---------+----------------+

I want to delete data from table1 and table2 in one query by criteria
WHERE table1.ts < UNIX_TIMESTAMP() - 30 * 24 * 60 * 60 AND table1.user_id = table2.user_id AND table1.who_id = table2.owner_id

Need a query similar to this but with LIMIT:

DELETE t1, t2 
FROM table1 t1, table2 t2 
WHERE t1.ts < UNIX_TIMESTAMP() - 30 * 24 * 60 * 60 
AND t1.user_id = t2.user_id AND t1.who_id = t2.owner_id;

Columns id does not intersect

Giffo
  • 4,161
  • 3
  • 15
  • 16
  • does http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql help? – ADyson Aug 10 '16 at 21:33
  • The big problem with the `LIMIT`. I saw these answers they will not help me – Giffo Aug 10 '16 at 21:50
  • The tables have 30 million records – Giffo Aug 10 '16 at 21:53
  • do you have any notion how long it will take to do the deletion? It'll probably be a bit faster if you use joins rather than the syntax you've got above. If it's a production system and the delete will take too long, maybe you need to schedule some downtime. – ADyson Aug 10 '16 at 22:19
  • The MySQL docs quite clearly state that you can't use LIMIT with multi-table deletes. http://dev.mysql.com/doc/refman/5.7/en/delete.html – ADyson Aug 10 '16 at 22:20
  • Can you show how it looks with join? All the same, I'm probably going to break on two requests for each table and for table2 with join – Giffo Aug 10 '16 at 22:26
  • @ADyson looks like a join to me, albeit non explicit syntax join – Drew Aug 11 '16 at 02:50
  • rephrasing your query using `INNER JOIN` : `DELETE t1, t2 FROM table1 t1 INNER JOIN table2 t2 ON t1.user_id = t2.user_id AND t1.who_id = t2.owner_id WHERE t1.ts < UNIX_TIMESTAMP() - 30 * 24 * 60 * 60 ` – 1000111 Aug 11 '16 at 05:07
  • @Drew would the DB engine treat it the same way though? Maybe I was mis-educated, but I was always taught that explicit joins were more effective than implicit joins via the where clause – ADyson Aug 11 '16 at 08:11

0 Answers0