CODEWITHSUNDEEP
phpmysqlsqlinnodbsql-delete
Acidon
Acidon

Reputation: 1334

Delete multiple records with different identifiers from a large table without using loop mysql php

I have a million rows table. I need to delete all the rows that have a certain column equal to one of values from 15k long list.

Right now I have it setup as:

$list = array(
"Item1",
...
"Item15000"
);

foreach ($list as $item){

$sql = "Delete from table where `item`='$item'";

if ($con->query($sql) === TRUE) {
            echo "$item removed from table <br>";
        } else {
            echo "Error removing $item from shop_meta: " . $con->error . "<br>";
        }

}

In its current state it takes many many hours to do it, and I have to do it for many other similar tables as well.

How do I speed it up or optimize the query? Is there a way to query them all at once to get it done faster?

Upvotes: 2

Views: 182

Answers (2)

Rick James
Rick James

Reputation: 142296

Plan A:

DELETE FROM t WHERE item IN (...long list of ids...)

Plan B:

Do plan A, but in chunks of 1000 ids. This will be friendlier to other users and replication (if relevant). Suggest running with autocommit=ON.

More tips on big deletes.

Upvotes: 1

juergen d
juergen d

Reputation: 204774

Put those 15k list in a table and then join against it

Delete t
from your_table t
join temp_table tt on t.item = tt.delete_value

Upvotes: 1

Related Questions