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