[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Help!
From:       "Ananda Kumar" <anandkl () gmail ! com>
Date:       2008-09-26 5:51:27
Message-ID: 829b199c0809252239l9bd8fc4x3a2fe381db978c34 () mail ! gmail ! com
[Download RAW message or body]


If the table has a primary key column then i would do this.

cursor cursor_name select primary_key_column from tabl_name where id <
3000000;
open <cursor_name>
fetch <cursor_name> into a_variable;
delete from table_name where primary_key_column=a_variable;
commit for every 1000 records;
exit when cursor does not fetch any more records;
close cursor;

This will allow deleting/commiting small set of data and also avoid building
huge rollback segments.





On 9/26/08, Martin Gainty <mgainty@hotmail.com> wrote:
>
>
> 2 items
> I would increase bump key_buffer_size as suggested here
> http://dev.mysql.com/doc/refman/5.1/en/delete-speed.html
>
> also TRUNCATE instead of DELETE where TRANSACTION is not active
>
> Martin
> ______________________________________________
> Disclaimer and confidentiality note
> Everything in this e-mail and any attachments relates to the official
> business of Sender. This transmission is of a confidential nature and Sender
> does not endorse distribution to any party other than intended recipient.
> Sender does not necessarily endorse content contained within this
> transmission.
>
>
> > Date: Thu, 25 Sep 2008 15:45:00 -0700
> > From: andrewmchorney@cox.net
> > To: gpeel@ody.ca; mysql@lists.mysql.com
> > Subject: Re: Help!
> >
> > Hello
> >
> > I would not recommend delete massive numbers of records in a single sql
> statement. I have had problems doing this on a system with few records (1.7
> million) and doing a delete with a condition clause of "where 1 = 1".  The
> server had problems with this.
> >
> > The MySQL server is probably building some rollback code in case
> something goes wrong. If it succeeds it will be a good amount of time. I
> would recommend doing several small deletes.
> >
> > Andrew
> >
> >
> >
> > ---- Grant Peel <gpeel@ody.ca> wrote:
> > > Hi all,
> > >
> > > I have a table I needed to prune because it had grown to 5 million rows
> and
> > > it appeared that that app that uses it couldnt anymore.
> > >
> > > I am in the process of:
> > >
> > > DELETE FROM mytable WHERE id < 3000000;
> > >
> > > the above has been running for close than an hour and I am starting to
> get a
> > > little nervous.
> > >
> > > it is on freebsd 6.3, mysql 4.x on a Single core dell 2.8 GHx I think.
> > >
> > > I have shut down all process but necessary one so mysql has full access
> to
> > > the cpu.
> > >
> > > Should I be worried? Is there anything I can do to speed this up?
> > >
> > > -G
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=andrewmchorney@cox.net
> > >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=mgainty@hotmail.com
> >
>
> _________________________________________________________________
> See how Windows connects the people, information, and fun that are part of
> your life.
> http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/


[prev in list] [next in list] [prev in thread] [next in thread]