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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
