List:General Discussion« Previous MessageNext Message »
From:mos Date:November 9 2011 3:40pm
Subject:Re: Deleting Records in Big tables
View as plain text  
If you could use MyISAM tables then you could use Merge Tables and 
create a table for each day (or whatever period you are collecting 
data for). Then when it is time to get rid of the old data, drop the 
oldest table (T20111101 or T10 for 10 days ago) and create a new 
empty table for the new day, and redefine the Merge table definition. 
This can be done in under 1 second.  You have the ability to access 
the Merge Table directly or each individual table that makes up the 
Merge Table. (The Merge table is a logical representation of MyISAM 
tables and requires no data copying).

Mike

At 01:22 AM 11/4/2011, Adarsh Sharma 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

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