MySQL delete across multiple tables

Question

I am working on a cron script (in PHP PDS for a MySQL db) that will delete data older than 30 days from the database. Ive tried to do joining and on delete cascaded on my tables and statements but they haven't worked out so I went with a simpler approach here to get it working however I know this isn't the most processor effective method.

Please note that some of these tables (especially the links table) have hundreds of thousands of rows so that's why I wanted to be a little more clever than what I have here.

The table structure is easy, there is a table key table that has a time stamp and an id. That id is repeated in all tables as tablekey_id.

My current cron job is as follows.

/* Cron script to delete old data from the database. */
if (@include dirname(dirname(dirname(__FILE__))) . '/dbconn/website_connections.php') {
    $conn = $connectionFromOtherFile;
    $keyTable = 'table_key';
    $linksTable = 'links';
    $domainsTable = 'link_domains';
    $terms = 'searched_domains';

    $query = $conn->query("SELECT `id` FROM `$keyTable` WHERE `timestamp` < (NOW() - INTERVAL 30 DAY)");
    $query->setFetchMode(PDO::FETCH_ASSOC);

    while($row = $query->fetch()) {
        $conn->exec("DELETE FROM `$linksTable` WHERE `tablekey_id` = $row[id]");
        $conn->exec("DELETE FROM `$domainsTable` WHERE `tablekey_id` = $row[id]");
        $conn->exec("DELETE FROM `$terms` WHERE `tablekey_id` = $row[id]");
        $conn->exec("DELETE FROM `$keyTable` WHERE `id` = $row[id]");
    }
}

Is there any way to get this into one statement? Please and thank you for the assistance.

EDIT: Heres what I ended up with.

/* Cron script to delete old data from the database. */
if (@include dirname(dirname(dirname(__FILE__))) . '/dbconn/website_connections.php') {
    $conn = $connectionFromOtherFile;
    $keyTable = 'table_key';
    $linksTable = 'links';
    $domainsTable = 'link_domains';
    $terms = 'searched_domains';
         $delete = $conn->prepare("DELETE tablekey, linktable, domaintable, searched
                            FROM `$keyTable` AS tablekey
                            LEFT OUTER JOIN `$linksTable` AS linktable on tablekey.id = linktable.tablekey_id
                            LEFT OUTER JOIN `$domainsTable` AS domaintable on tablekey.id = domaintable.tablekey_id
                            LEFT OUTER JOIN `$terms` AS searched on  tablekey.id = searched.tablekey_id
                            WHERE tablekey.id = :tablekey_id");

    $query = $conn->query("SELECT `id` FROM `$keyTable` WHERE `timestamp` < (NOW() - INTERVAL 30 DAY)");
    $query->setFetchMode(PDO::FETCH_ASSOC);

    while($row = $query->fetch()) {
        $delete->execute(array('tablekey_id' => $row['id']));
    }
}

Why are you using query string building with PDO? It supports prepared statements, which have performance advantages in a loop and are less susceptible to SQL injection — GordonM, Feb 21 '13 at 16:22
possible duplicate of [Mysql - delete from multiple tables with one query](http://stackoverflow.com/questions/4839905/mysql-delete-from-multiple-tables-with-one-query) — Colin M, Feb 21 '13 at 16:23
@AndreiFilonov I tried to create tables with foreign key constants and cascade delete. I spent several hours working with these four tables and I could not get them to work. So I switched to this because at some point I had to move on to another task. — RachelD, Feb 21 '13 at 16:25
@GordonM Your absolutely correct. I'm changing that right now. I was just going for the most direct approach because I couldn't get the on delete cascade thing working and didn't think about it. — RachelD, Feb 21 '13 at 16:26
@RachelD For foreign keys to work, the data types _must_ match (definitely look at _unsigned_ vs _signed_ if these are integer columns), and all tables must be InnoDB. — Colin M, Feb 21 '13 at 16:27
@RachelD Please look at the question referenced in my comment. The second answer on the page (not the accepted one) indicates how to do a multi-table delete if you absolutely can't get foreign keys working. — Colin M, Feb 21 '13 at 16:30

score 5 · Accepted Answer · answered Feb 21 '13 at 16:33

5

You should be able to delete with one query like this:

DELETE kt, lt, dt, t
FROM `$keyTable` AS kt
LEFT OUTER JOIN `$linksTable` AS lt on kt.id = lt.tablekey_id
LEFT OUTER JOIN `$domainsTable` AS dt on kt.id = dt.tablekey_id
LEFT OUTER JOIN `$terms` AS t on  kt.id = t.tablekey_id
WHERE kt.id = $row[id]

Note that I used outer joins here as I wasn't sure if the tables other than keyTable would be guaranteed to have records with that tablekey_id. If they are, you could use an inner join.

answered Feb 21 '13 at 16:33

Mike Brant

70,514
10
99
103

I am having exact the same situation when the records are not guaranteed to be present in other tables and getting "Lost connection to MySQL server during query" error. – user109764 Nov 19 '14 at 17:18
@user109764 There could be any number of things that cause that. Maybe you have bad indexes and your query is taking too long and you client times out. Maybe your database server is having problems. Who knows. It likely has nothing to do with this sort of LEFT JOIN query itself. You probably need to look through your MySQL logs to start investigating the problem. – Mike Brant Nov 19 '14 at 22:12

score 1 · Answer 2 · answered Feb 21 '13 at 16:25

If tablekey_id is indexed, this should be fine even with thousands of rows. If you want to keep your database schema simple, I would keep 4 queries, deleting is not that CPU intensive. If you really want one statement, you will have to complicate things and use a cascading deletion with foreign keys constraints:

CASCADE: Delete or update the row from the parent table, and automatically delete or update >the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

Source: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

And this is admitting that your database is innoDB

score 1 · Answer 3 · answered Feb 21 '13 at 16:28

1

first you want to retrieve the list of IDs like you are doing now, then you want to construct 4 queries like this:

DELETE FROM 'table' where 'id' IN ('id1', 'id2', 'id4')

this should prove a LOT more efficient then doing separate deletes. It would reduce the amount of queries to 5 in stead of 1+ 4N

answered Feb 21 '13 at 16:28

Hendrik

1,355
3
11
30

Actually this is what I do - when I don't want a trigger! - combined with prepared statements. – Brian Feb 21 '13 at 16:29

score 0 · Answer 4 · answered Feb 21 '13 at 16:24

0

As far as I know PDO only accepts one statement...

You could look at a trigger on deleting from the first (or you're main) table...

answered Feb 21 '13 at 16:24

Brian

8,418
2
25
32

FWIW, PDO accepts multiple statements by default. I wish it didn't -- it creates more risk for SQL injection! – Bill Karwin Feb 21 '13 at 16:53

score -1 · Answer 5 · answered Feb 21 '13 at 16:51

Sometimes I use this to solve my problems. Put the ids in string first.

$ids = array();
while($row = $query->fetch()) {
    $ids[] = $row[id];
}
$ids_str = implode(',', $ids);

$conn->exec("DELETE FROM `$linksTable` WHERE `tablekey_id` in ($ids_str) ");
$conn->exec("DELETE FROM `$domainsTable` WHERE `tablekey_id` in ($ids_str)");
$conn->exec("DELETE FROM `$terms` WHERE `tablekey_id` in ($ids_str)");
$conn->exec("DELETE FROM `$keyTable` WHERE `id` in ($ids_str)");

MySQL delete across multiple tables

5 Answers5