 | | | Subject: Re: Which is faster when deleting rows? In() or Separate Delete stmts? | Subject: Re: Which is faster when deleting rows? In() or Separate Delete stmts? 2007-11-03 - By Baron Schwartz
Back
Hi
mos wrote:
> If I have a large table with 20 million rows, is it going to be faster
> to use one delete statement like:
>
> delete from mytable where rcdid in(20,300,423, .... 99999)
>
> to delete 10-100 random records using the primary index "RcdId"
>
> or should I use separate delete statements for each RcdId as in:
>
> delete from mytable where rcdid = 20;
> delete from mytable where rcdid = 300;
> delete from mytable where rcdid = 423;
> ...
> delete from mytable where rcdid = 99999;
>
> I'm concerned the IN() clause will cause a full table scan and that will
> take quite a while to find the rows even though I'm using the primary
> key RcdId.
You can convert it into an equivalent SELECT and use EXPLAIN to see if
it's using a table scan.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|