List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 16 2005 4:57pm
Subject:Re: Can I dissable transactions?
View as plain text  
Ben Clewett wrote:

> Jeff,
> 
> Thanks for your idea.  Deleting data x rows at a time would certainly 
> help, if AUTOCOMMIT=TRUE.  But I have no idea how :)  I have tried:
> 
> DELETE FROM very_large_table WHERE delete_flag=1 LIMIT 100000;
> 
> But the LIMIT is not understood (4.1.8).

What do you mean?  That appears to be valid syntax.  Do you get an error or 
unexpected results?  (You might also want to try a smaller limit).

> Unfortunately my 'delete_flag' is not key.  Therefore repeated attempts 
> at deleting small amounts of data is very slow.

Right, no index on delete_flag means a table scan.  The LIMIT N should cause 
mysql to stop as soon as it finds (and deletes) the Nth matching row, 
though.  The trick is to set N low enough to get the speed you need. 
Unfortunately, each run will probably take longer than the one before, as 
there will be farther to go to find the first match.

I expect delete_flag is either 0 or 1.  What percent of rows have 
delete_flag = 1?  If small enough, an index on delete_flag would be used for 
this statement to speed things up.  Of course, that's an extra index 
probably not used for anything else.

If you haven't read them already, you may find these pages from the manual 
helpful:
<http://dev.mysql.com/doc/mysql/en/delete.html>
<http://dev.mysql.com/doc/mysql/en/delete-speed.html>

> The fastest way seem to be to dump the data, edit the file, and 
> re-insert the data.
> 
> But you have given my my solution:
> 
> If I cannot disable transactions, I'll have to work with one of the keys 
> and iterate through that key bit bit.  So thanks, I'll go off and give 
> it a go...
> 
> Ben.

Michael

Thread
Can I dissable transactions?Ben Clewett14 Feb
  • Re: Can I dissable transactions?Jeff Smelser14 Feb
    • Re: Can I dissable transactions?Ben Clewett15 Feb
      • Re: Can I dissable transactions?Michael Stassen16 Feb