List:General Discussion« Previous MessageNext Message »
From:Carsten Pedersen Date:April 14 2010 6:20pm
Subject:Re: Make delete requests without impact on a database
View as plain text  
Been there, done that. It's a maintenance nightmare.

Another idea: Have a separate "deleted" table with the IDs of the rows 
that you consider deleted. Re-write your queries to do a 
left-join-not-in-the-other-table agains the "delete" table. Then, either 
wait for a maintenance window to delete the rows both the original table 
and the "delete" table. Or remove just a few rows at a time.

The "deleted" table can be created with just a read lock on the original 
table, and since it's going to be fairly small, the impact of stuffing 
it with data is not going to be great.

It's a bit of a hassle to set up, but once done you don't have to worry 
about creating and deleting tables every day.

/ Carsten

mos skrev:
> It looks like you only want to keep the current data, perhaps the 
> current day's worth, and delete the old data.
> 
> I would store the data in separate MySIAM tables, each table would 
> represent a date, like D20100413 and D20100414. Your program will decide 
> which table to insert the data into by creating a current date variable 
> and now all data gets inserted to the table named by that variable. When 
> the older data is no longer needed, just drop the table. If you want to 
> keep the last 7 days of data, create a merge table of the last 7 tables. 
> When you drop the oldest table, redefine the merge table. You can 
> accomplish all this in just milliseconds.
> 
> Mike
> 
> 
> 
> At 08:08 AM 4/14/2010, you wrote:
>> Hi,
>>
>>
>>
>> I am using MySQL  version 4.1.12-log. All the databases on it are using
>> MyISAM database engine.
>>
>>
>>
>> Every day, I delete almost 90000 rows on a table of 3 153 916 rows.
>>
>>
>>
>> To delete the rows, I use a request like this : "DELETE QUICK FROM 
>> [table]
>> WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this 
>> request
>> until all the rows are delete.
>>
>>
>>
>> This works but when I run the request, I can't access to the database 
>> (make
>> INSERT and SELECT requests) during I do the DELETE.
>>
>>
>>
>> How can I do a "DELETE" without impact on INSERT and SELECT requests 
>> done on
>> the same time?
>>
>>
>>
>> Regards,
>>
>>
>>
>> David.
>>
>>
> 
> 
Thread
Make delete requests without impact on a databaseDavid Florella14 Apr
  • Re: Make delete requests without impact on a databaseDan Nelson14 Apr
    • RE: Make delete requests without impact on a databaseDaevid Vincent15 Apr
    • Re: Make delete requests without impact on a databasePerrin Harkins15 Apr
      • RE: Make delete requests without impact on a databaseDavid Florella15 Apr
  • Re: Make delete requests without impact on a databasemos14 Apr
    • Re: Make delete requests without impact on a databaseCarsten Pedersen14 Apr
      • Re: Make delete requests without impact on a databasemos14 Apr
        • Re: Make delete requests without impact on a databaseCarsten Pedersen14 Apr