I have a table tbl_transaction getting around 3000 entries per minute.
I copy the data to another table tbl_transaction_history every minute and delete tbl_transaction entries which are more than 3 months old.
To copy data from tbl_transaction to tbl_transaction_history, I use mysql Query-
INSERT IGNORE INTO tbl_transaction_history select * from tbl_transaction;
Is it the best way of doing this?
tbl_transaction have millions of records with primary key as ID. So, while running the query will mysql try to insert ignore every row from tbl_transaction to tbl_transaction_history again and again? OR Mysql will only pick the rows not copied and only copy them?
basically, I tried to know but cant found that How will this query work every minute? If, this query is not efficient, what should I do to achieve the same.