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.
>>
>>
>
>
