List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:November 4 2011 4:47pm
Subject:Re: Deleting Records in Big tables
View as plain text  
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.
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