List:General Discussion« Previous MessageNext Message »
From:Adarsh Sharma Date:November 4 2011 8:44am
Subject:Re: Deleting Records in Big tables
View as plain text  
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
>
>


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