Re: Deleting Records in Big tables

[Ananda Kumar]

Why dont you create a new table where id < 2474,
rename the original table to "_old" and the new table to actual table name.

or
You need to write a stored proc to loop through rows and delete, which will
be faster.

Doing just a simple "delete" statement, for deleting huge data will take
ages.

regards
anandkl

On Fri, Nov 4, 2011 at 12:52 PM, Adarsh Sharma <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=anandkl@ style="color:#666">stripped<http://lists.mysql.com/mysql?unsub=1
>
>