MySQL – INSERT … SELECT .. FROM .. – Single Transaction or Transaction Per Row?

innodbMySQLtransaction

Question:

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).

Best Answer

You do not want to do one monolithic transaction because if the transaction fails for any reason, mysqld will spend a long time rolling back everything from the undo logs. There are 1023 undo logs in the system tablespace file (a.k.a. ibdata1). Here is what InnoDB looks like:

InnoDB Architecture

As I said before, there are 1023 undo logs (a.k.a. Rollback Segments). The amount of info stored to preserve the state of new_table would fill up just one undo log. It's not worth the effort and firepower to load everything as a single transaction and a single commit loading one undo log in the event of a crash that may never happen.

Please script the retrieval from old_table to do something like 10000 rows at a time. Since autocommit=1 by default, a single commit will be 10000 rows. For 2.5 million rows, that would be 250 commits.

Related Solutions

Sql-server – Will a delete then insert within a transaction cascade

I'm only familiar with SQL Server:

Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.

If you're relying on the ID values and don't want to cascade the related tables, consider switching to a merge strategy (where you use UPDATE or, preferably MERGE) instead of blowing everything away and starting from scratch.

I suppose you could try something like disabling the relationships during data loading, but given what I think you're trying to do, that seems fraught with peril. Definitely try a merge strategy first.

Mysql – Select “,* from table

You can do the following:

CREATE TABLE tablename_temp (id int AUTO_INCREMENT PRIMARY KEY)
ENGINE=InnoDB 
SELECT * FROM tablename;

ALTER TABLE b
ADD UNIQUE INDEX some_index_name (your_original_key);

You can find the documentation here: http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html And a small example on SQLFiddle.

Best Answer

Related Solutions

Sql-server – Will a delete then insert within a transaction cascade

Mysql – Select “,* from table

Related Question