List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:November 4 2011 4:56pm
Subject:Re: Deleting Records in Big tables
View as plain text  
Excellent point... replication  makes many things trikier

On 11/4/11 9:54 AM, Derek Downey wrote:
> 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
>>
>
>

-- 
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.
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