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