What is the best way to run a number of sql commands to archive and delete data

I am new to mysql so forgive me if the solution to my problem is obvious. I have a mysql database that has a number of tables that I need to archive. A number of the tables have over 1000000 rows. I have 11 tables that I am archiving and then deleting from.

A snippet from my code would be :

set @archive_before_date='2010-10-01'

create table if not exists archive_prescription as 
select * from prescription where prescriptiondate < @archive_before_date;

delete from prescription where prescriptiondate < @archive_before_date;

I am currently using mysql browser and I am running the script in the script tab. When I run the script the mysql query browser shows in task manager as not responding and mysql administrator shows only the first two archive tables. On a database with the same schema开发者_如何转开发 and less data the script works perfectly.

Does anyone have any hints as how to best approach this problem?

---

Copy the script into a file and save it to disk.

Then run mysql -u -p < yoursavescriptname

This will read the commands in from the script and execute them in turn.

This command line itself could be placed in a .bat file on Windows, or an executable .sh file on *nix.

Also you could place several mysql etc. < filename lines in the same .bat files.

If you wanted to run this at a certain time you could then reference the .sh file from a cron entry. however, if doing this add an env in the .sh file somewhere, and redirect the output to a file as cron runs with a different environment to a logged in user.

---

If the prescriptiondate field is not indexed it will take a long time and lock the table while it runs. So try adding an index.

---

Try the command line mysql tool

EDIT: add example

mysql -uusername -ppassword yourshema < your.sql

---

I would recommend that you try to deleting a fixed number of rows at a time. I haven't used mysql yet but here is an example from MS SQL.

DECLARE @DELETE_COUNT INT, @ARCHVIE_DATE DATETIME

SET @ARCHIVE_DATE = '2010-10-01'
SET @DELETE_COUNT = 1000

WHILE (@DELETE_COUNT <> 0)
BEGIN

DELETE TOP (@DELETE_COUNT)
OUTPUT DELETED.*
INTO MyArchiveTable
FROM MyTable a
WHERE a.PrescriptionDate < @ARCHIVE_DATE

SET @DELETE_COUNT = @@ROWCOUNT

END

This has the advantage of deleting and archiving the data in one statement. If MySQL does not have something similar, your plan is totally reasonable. You need to save the top 1000 rows then delete them. Please ensure that you have an order by ... in case someone changes the clustered index on you and obviously an index on PrescriptionDate. If possible, you may want to create a clustered index on PrescriptionDate. Or You may want to add prescription date to an existing clustered index as the first column.

DECLARE @DELETE_COUNT INT, @ARCHVIE_DATE DATETIME

SET @ARCHIVE_DATE = '2010-10-01'
SET @DELETE_COUNT = 1000

WHILE (@DELETE_COUNT <> 0)
BEGIN

BEGIN TRANSACTION

INSERT INTO MyArchiveTable
SELECT TOP(@DELETE_COUNT) * FROM MyTable a WHERE a.PrescriptionDate < @ARCHIVE_DATE
ORDER BY a.PrescriptionDate

DELETE FROM MyTable a INNER JOIN
(SELECT TOP(@DELETE_COUNT) * FROM MyTable
WHERE PrescriptionDate < @ARCHIVE_DATE ORDER BY PrescriptionDate) b ON a.Id = b.Id

SET @DELETE_COUNT = @@ROWCOUNT

COMMIT TRANSACTION    
END

This also has the added advantage of not rolling back your entire delete in case there is a deadlock. If MySQL has try/catch statements, you may want to use those to trap deadlock errors and then ignore them. The while loop should keep trying over and over again until it succeeds.

Please note that I have never used MySQL so my syntax may not be right.

Hope this helps.