3

Table A which is always getting updated (records being inserted or updated). Table A contains millions of records. I'd like to copy some of these records to a new table Table B. Table A and Table B has exact same schema.

How can I copy records from Table A to Table B? I don't want to consider the data which keeps getting updated in Table A. I only want to copy the data which is there when I first queried Table A. I'm trying to copy data in batches. So everytime I query for a batch of 500 records from Table A and copy them to Table B. The next time I query Table A to get next 500 records using offset. There is no guarentee that the new set of records are exactly next batch of 500 records since Table A is always getting updated. The task is to be able to ensure we are fetching batches in sequential way and it guarentees we have exactly next 500 records.

INSERT INTO `Table B` FROM SELECT * FROM `Table A` WHERE ...

doesn't work. Because as I mentioned Table A has a lot of data and running this query timesout. It needs to be carried out in batches. Creating a temporary table would also require to copy it in batches.

I tried to use mySQL views but they also have the same problem. The view fetches data from the underlying table. If the underlying table gets updated the view fetches the updated data.

6
  • Would it be possible to create an intermediate table containing only the primary keys of table A? Commented Nov 10, 2021 at 9:43
  • It would still require fetching in batches. Fetching millions of primary keys in a single query wouldn't work. Commented Nov 10, 2021 at 9:55
  • Does source table (tableA) contains autoincremented PK column? if not - maybe autoutilized created_at exists?
    – Akina
    Commented Nov 10, 2021 at 10:43
  • Block the updates while you are creating table B? Commented Nov 10, 2021 at 11:35
  • blocking the updates is not an option, unfortunately. Commented Nov 10, 2021 at 11:57

2 Answers 2

1

Short Answer: Do not use OFFSET. Instead, "remember where you left off".

Long Answer: See 2 of my blogs:

http://mysql.rjweb.org/doc.php/pagination -- Pagination via OFFSET can skip or duplicate rows. And it is inefficient.

http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks -- Efficient chunking -- Preferably via the PRIMARY KEY. It discusses how to handle a multi-column PK.

Alternate answer: Use mysqldump to dump just the table and avoid the CREATEs. Then use mysql to reload that dump into a different table.

1
  • Ok, when querying, you can "note down" the last primary key retrieved. But when you do a INSERT INTO TableB select * from TableA limit ${chunkSize} offset ${seqIdx} equivalent query, how would you acquire the last inserted value? Oh wait, I got it, you simply select the last value in Table B!
    – pihentagy
    Commented May 31, 2022 at 14:33
2

Have you considered Table Partitioning? It's seems that you are trying to do by hand what MySQL will do for you automatically.

In any event you will need to get your data into the new partitioned table or the old data into your archive table. I agree with Rick James - find a key (primary key would work well for this) and move small chunks using that key not limit and offset.

You did not say it you are going to do this often (in which case please read up on partitions) or this is a one time thing. If it's a one time thing you can even write a single script:

INSERT INTO foo SELECT * FROM bar WHERE id between 0 and 5000;
INSERT INTO foo SELECT * FROM bar WHERE id between 5001 and 10000;
INSERT INTO foo SELECT * FROM bar WHERE id between 10001 and 15000;
INSERT INTO foo SELECT * FROM bar WHERE id between 15001 and 20000;
...

You probably want to delete from the bar table and you'd like to be sure those rows were indeed copied to the foo table so you might instead try:

INSERT INTO foo SELECT * FROM bar WHERE id between 0 and 5000;
DELETE FROM bar WHERE id in (SELECT id FROM foo);
INSERT INTO foo SELECT * FROM bar WHERE id between 5001 and 10000;
DELETE FROM bar WHERE id in (SELECT id FROM foo);
INSERT INTO foo SELECT * FROM bar WHERE id between 10001 and 15000;
DELETE FROM bar WHERE id in (SELECT id FROM foo);
INSERT INTO foo SELECT * FROM bar WHERE id between 15001 and 20000;
DELETE FROM bar WHERE id in (SELECT id FROM foo);
...

If 5000 rows is still too big try 500, 100 or even 10. You can try different numbers before you write the whole script. Remember the data you move will not need to be moved again so you can start over at 0 without harm. This script can run for a long time; if you really want to have minimal impact put a SELECT sleep(1) after each INSERT. Remember in this example id really needs to be in some kind of index.

Good Luck, Frank

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.