I'm running MariaDB 10.3.32, which I understand is more or less identical to MySQL.

I have a table that's 2.5G in size (2.1G data, 433M index) and has 10.4 million rows. A lot of this data can be safely archived away, though, which I'm hoping will make the main table process queries faster.

How would you suggest moving it in the fastest to process method possible? This is a live table that has inserts and selects 24/7, so I really can't justify any real amount of down time.

My initial thought was to create "archive" with the same structure, then run a query like:

insert into archive
 select * from tableA where date <= 20201117000000;

delete from tableA where date <= 20201117000000;

But I think that would probably be the SLOWEST to process! It might even time out on me.

My next thought... copy /var/lib/mysql/example/tableA.frm, tableA.MYD, and tableA.MYI to a /backup directory, rename the files to archive.frm/MYD/MYI, then move them back to /var/lib/mysql and overwrite the existing archive files.

Then go to phpMyAdmin and delete anything in "archive" that I don't want stored in the archive, then delete the archived info from tableA:

delete from archive where date > 20201117000000;
delete from tableA where date <= 20201117000000;

This might still be a lot slower than I want, and I don't love that there's no safety in place to make sure that I'm not deleting something that's not archived.

Any other suggestions?