my $sth = $dbh->prepare("DELETE FROM table WHERE message_id = ?");
foreach $i (@MESSAGE_IDS){
  $sth->execute($i);
}
[download]

I don't see why using 'or' or 'in' would be slower, unless there's something about MySQL in particular I don't know about (which is entirely possible). It should be quicker since there's only one call to the database instead of many, and it should still be able to take advantage of any existing indexes.

In general DB servers aren't good at optomizing "in" or "or" queries. The server may end up doing a table scan to locate all the items to delete, which could be really slow. By doing each ID individualy you're guaranteed that each one will go directly to that item (assuming there's an index on the ID col) and delete it. True you are executing more statments, but I suspect that in many situations it could be quicker than the poorly optomized "in" statment.

Benchmarks would be interesting ;)

All of these worked, thanks. I had to take out the DELETE *, apparently, you just say DELETE FROM TABLE, and you have to use the WHERE clause to narrow down the columns. Red------

$elements = join('","',@MESSAGE_IDS);
$elements = '"'.$elements.'"';
my $sth = $dbh->prepare("DELETE * FROM table WHERE message_id in ($ele
+ments)");
$sth->execute();
[download]

my $sth=$dbh->prepare("DELETE FROM table WHERE ".
          join(" or ",map { "message_id = $_" } @MESSAGE_IDS));
[download]

while (@MESSAGE_IDS) {
    my @todel=splice @MESSAGE_IDS,0,10;
    my $sth=$dbh->prepare("DELETE FROM table WHERE ".
          join(" or ",map { "message_id = $_" } @todel));
    $sth->execute;
}
[download]

and/or consider using join to form a sql statement (still with placeholders) such as:

my $tmp = join "," map "?", @arr;
my $stmt = <<"EOT";
delete from table_name where field in ($tmp)
EOT

my $sth = $dbh->prepare($stmt);
$sth->execute(@arr);
[download]

Update:It's a delete statement. duh.

Why not just glom all your ids into one DBI statement, so that it looks like:

delete * from table where (id=1 or id=2 or id=3)

Example, extremely untested code:

$query = "delete from table where id=(";

for(@MESSAGE_IDS){
# you may need a "chomp" in here
  $query.=$_." or id=";
}
#Delete "or" off the back
$query=substr($query,0,-7));
#close the query
$query.=")";

my $sth= $dbh->prepare($query);
$sth->execute;
[download]

I wouldn't use this for huge deletes, but it should serve your purposes. If the "id" column is indexed, this should be pretty damn fast.

my $sth = $dbh->prepare("DELETE * FROM table 
                                   WHERE message_id = ?");

while $i (@MESSAGE_IDS){
          $sth->bind($i)
          $sth->execute();
          # and so on ...
        }
[download]

Remember, you only need to prepare a statement handle if you are going to re-use it for more than 1 query. Otherwise, you can use the Database Handle Methods for one-time queries. They encapsulate a prepare, execute, and sometimes a fetch-type method in a single call. For INSERT or UPDATE statements use DBI::do.

The following statement does a batch DELETE without using any 'Synthetic' Code, and is tested to work with MySQL:

$dbh->do(
  sprintf(
    'DELETE FROM table WHERE message_id IN(%s)', 
    join ',', ('?') x @MESSAGE_IDS
  ),
  {},
  @MESSAGE_IDS,
);
[download]