- #1
cpscdave
- 403
- 120
Hi everyone,
I have a MySQL DB with a couple of large tables (a couple hundred million rows). Each row has a timestamp associated with it. Since this is just historical data, and not needed we want to copy older data into another database, and then delete the copied records.
I've written a python script that accomplishes this by running a series of queries:
What is the most efficent way to run these queries?
I've tried running it so that we timestamp is the ultimate goal value, but the query takes a long time to run, and can timeout on some of the larger tables,
So I've changed the script to try and do the same thing but looping through the oldest value + 1 month repeatedly till we hit the goal.
Or is there a third way that would be better??
Thanks,
-Dave
I have a MySQL DB with a couple of large tables (a couple hundred million rows). Each row has a timestamp associated with it. Since this is just historical data, and not needed we want to copy older data into another database, and then delete the copied records.
I've written a python script that accomplishes this by running a series of queries:
Code:
CREATE DATABASE IF NOT EXISTS `olddata`;
CREATE TABLE IF NOT EXISTS `oldTable`;
INSERT INTO `olddata`.oldeTable` SELECT * FROM `currData`.`currTable` WHERE timestamp < someValue;
DELETE FROM `currData`.`currTable` WHERE timestamp < someValue;
What is the most efficent way to run these queries?
I've tried running it so that we timestamp is the ultimate goal value, but the query takes a long time to run, and can timeout on some of the larger tables,
So I've changed the script to try and do the same thing but looping through the oldest value + 1 month repeatedly till we hit the goal.
Or is there a third way that would be better??
Thanks,
-Dave