I am currently working on a data migration. I need to migrate 2.5 million rows from one InnoDB table to another InnoDB table (with a different setup). I am using the following syntax to do that:
INSERT `new_table` (`new_column`,`more_columns`) SELECT `old_column`,`more_old_columns` FROM `old_table`;
I am wondering if MySQL/InnoDB will perform this as one giant transaction, or will MySQL/InnoDB create a transaction per row it encounters from the select? (I would basically would like to know if it will perform a COMMIT on each row).