Search code examples
phpmysqlsqlinnodbsql-delete

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?

Solution

  • 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