Re: Deleting Multiple MySQL Rows With DBI

The most efficient way is probably to so something like this:

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

Since the query is always the same, you only need to prepare it once. I suspect that doing it in one query whith an "or" or "in" using the entire list of numbers at once (depending on how many you're deleting) may not be well optomized by the server, and hence slower.

Comment on Re: Deleting Multiple MySQL Rows With DBI Download Code

Replies are listed 'Best First'.
Re: Re: Deleting Multiple MySQL Rows With DBI by runrig (Abbot) on May 22, 2001 at 23:43 UTC
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.	[reply]
Re: Re: Re: Deleting Multiple MySQL Rows With DBI by lhoward (Vicar) on May 23, 2001 at 00:15 UTC
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 ;)	[reply]
Thank you guys so much by Red Neckerson (Novice) on May 23, 2001 at 03:42 UTC
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------	[reply]