List:General Discussion« Previous MessageNext Message »
From:Derek Downey Date:November 4 2011 4:54pm
Subject:Re: Deleting Records in Big tables
View as plain text  
Be careful deleting with limit. If you're replicating, you're not guaranteed the same
order of those  you've deleted.

Perhaps a better way to delete in smaller chunks is to increase the id value:

DELETE FROM my_big_table WHERE id> 5000;
DELETE FROM my_big_table WHERE id> 4000;
etc

-- Derek

On Nov 4, 2011, at 12:47 PM, Andy Wallace wrote:

> I've had some luck in the past under similar restrictions deleting in
> chunks:
> 
> delete from my_big_table where id > 2474 limit 1000
> 
> But really, the best way is to buy some more disk space and use the
> new table method
> 
> On 11/4/11 1:44 AM, Adarsh Sharma wrote:
>> Thanks Anand,
>> 
>> 
>> Ananda Kumar wrote:
>>> Why dont you create a new table where id < 2474, rename the original table
> to "_old" and the new table to actual table name.
>> I need to delete rows from 5 tables each > 50 GB , & I don't have
> sufficient space to store extra data.
>> My application loads 2 GB data daily in my databases.
>>> 
>>> or
>>> You need to write a stored proc to loop through rows and delete, which will
> be faster.
>> 
>> Can U provide me a simple example of stored proc
>>> 
>>> Doing just a simple "delete" statement, for deleting huge data will take
> ages.
>> 
>> Even the Create Index command on ID takes hours too complete.
>> 
>> I think there is no easiest way to delete that rows from mysql tables.
>> 
>> 
>> 
>> 
>> 
>>> 
>>> regards
>>> anandkl
>>> 
>>> On Fri, Nov 4, 2011 at 12:52 PM, Adarsh Sharma <adarsh.sharma@stripped
> <mailto:adarsh.sharma@stripped>> wrote:
>>> 
>>> 
>>> Dear all,
>>> 
>>> Today I need to delete some records in > 70 GB tables.
>>> I have 4 tables in mysql database.
>>> 
>>> my delete command is :-
>>> 
>>> delete from metadata where id>2474;
>>> 
>>> but it takes hours to complete.
>>> 
>>> One of my table structure is as :-
>>> 
>>> CREATE TABLE `metadata` (
>>> `meta_id` bigint(20) NOT NULL AUTO_INCREMENT,
>>> `id` bigint(20) DEFAULT NULL,
>>> `url` varchar(800) DEFAULT NULL,
>>> `meta_field` varchar(200) DEFAULT NULL,
>>> `meta_value` varchar(2000) DEFAULT NULL,
>>> `dt_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
>>> PRIMARY KEY (`meta_id`)
>>> ) ENGINE=InnoDB AUTO_INCREMENT=388780373 ;
>>> 
>>> 
>>> Please let me know any quickest way to do this.
>>> I tried to create indexes in these tables on id, but this too
>>> takes time.
>>> 
>>> 
>>> 
>>> Thanks
>>> 
>>> -- MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>> 
>>> 
>> 
>> 
> 
> -- 
> Andy Wallace
> iHOUSEweb, Inc.
> awallace@stripped
> (866) 645-7700 ext 219
> --
> There are two ways to build software:
> Make it so simple that there are obviously no bugs,
> or make it so complex that there are no obvious bugs.
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
Deleting Records in Big tablesAdarsh Sharma4 Nov
  • Re: Deleting Records in Big tablesAnanda Kumar4 Nov
    • Re: Deleting Records in Big tablesAdarsh Sharma4 Nov
      • Re: Deleting Records in Big tablesAndy Wallace4 Nov
        • Re: Deleting Records in Big tablesDerek Downey4 Nov
          • Re: Deleting Records in Big tablesAndy Wallace4 Nov
  • Re: Deleting Records in Big tablesReindl Harald4 Nov
    • Re: Deleting Records in Big tablesAnanda Kumar4 Nov
      • Re: Deleting Records in Big tablesReindl Harald4 Nov
    • Re: Deleting Records in Big tablesJohan De Meersman4 Nov
  • Re: Deleting Records in Big tablesmos9 Nov
    • Re: Deleting Records in Big tablesJohan De Meersman10 Nov