Excellent point... replication makes many things trikier
On 11/4/11 9:54 AM, Derek Downey wrote:
Be careful deleting with limit. If you're replicating, you're not guaranteed
the same order
> of those you've deleted.
Perhaps a better way to delete in smaller chunks is to increase the id value:
DELETE FROM my_big_table WHERE id> 5000;
DELETE FROM my_big_table WHERE id> 4000;
etc
-- Derek
On Nov 4, 2011, at 12:47 PM, Andy Wallace wrote:
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.sha...@orkash.com<mailto:adarsh.sha...@orkash.com>> 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=anan...@gmail.com
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=de...@orange-pants.com
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
