CODEWITHSUNDEEP
mysqlselectinsert
fanchyna
fanchyna

Reputation: 2713

MySQL INSERT INTO SELECT very slow

I just created an empty table document_test which is a copy of the document table. I then add a new column host to document_test and copy everything else from document. The statement is like:

INSERT INTO document_test (id,col1,col2,col3,col4) SELECT * from document;

The document table contains about 5.5 million rows and I have disabled the keys in document_test, set foreign_key_checks to 0 and unique_checks to 0. This INSERT commands has been running for two days on a Linux server and has not been finished. The SHOW PROCESSLIST displays the state is "Sending Data". The SELECT count(*) FROM document_test; command returns 0. However, I do see from the SHOW TABLE STATUS command that the number of rows in document_test is non-zero. Interestingly, presumably, the row number should increase monotonously, but it is NOT. The results of four consecutive queries are 3299583, 3325775, 3299730 and 3341818!

This is something that completely confuses me. Could someone help me on: (1) is it inserting data? (2) how to finish this insert in a timely manner? (2) is there any way to check the status of this INSERT INTO SELECT command? (what next after "sending Data")

Unfortunately, I did not turn on the slow-query-log and profiling before running this command.

Upvotes: 2

Views: 3680

Answers (1)

Bartosz Grzybowski
Bartosz Grzybowski

Reputation: 1179

Maybe try exporting the data using mysqldump and then imprt it via LOAD DATA ? That should be way more faster.

Upvotes: 2

Related Questions