Copying large amount of data from one table to another

There are two tables: pos, temp_pos. The first table contains a little over 350 million records, the second about 3 million records. Required effectively to transfer data from one table to another. Interested in here are some moments:
1. What engine for this task is better suited InnoDB or MyISAM still?
2. Do you need to disable keys and enable it after? If it speeds up the process slows down or Vice versa?

Now I do it like this:
Pos ALTER TABLE DISABLE KEYS;
INSERT INTO pos (id, position, url_id, dataset_id) SELECT id, position, url_id, dataset_id from temp_pos;
Pos ALTER TABLE ENABLE KEYS;

Now uses MyISAM and the data migration operation takes on average from 40 minutes to 2.5 hours. I want to do it effectively.
October 3rd 19 at 04:37
More answers about "Copying large amount of data from one table to another"
6 answers
October 3rd 19 at 04:39
MyISAM for inserts should be faster than InnoDB ( www.opennet.ru/tips/info/1958.shtml )

try this method:
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SESSION tx_isolation='READ-UNCOMMITTED';
SET sql_log_bin = 0;
Pos ALTER TABLE DISABLE KEYS;

INSERT INTO pos (id, position, url_id, dataset_id) SELECT id, position, url_id, dataset_id from temp_pos;

Pos ALTER TABLE ENABLE KEYS;
SET sql_log_bin = 1;
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
SET SESSION tx_isolation='READ-REPEATABLE';
And yet, if the tables are identical in structure, you should try first to copy temp_pos into another temporary table, then temporary add the records from pos, delete, pos, and rename the temporary table in pos.
MySQL needs to optimizirovat full copy of Tabiti (insert into temp_pos_temp select * from temp_pos). Further, the insert 3 million should go much faster. - April_Wucker commented on October 3rd 19 at 04:42
What do say about working with such a large amount of data? The use of relational databases in their pure form are permissible here or better still to use any nesiruosia means in this case? - Miracle.Bahringer62 commented on October 3rd 19 at 04:45
Tell me more, what do you say about the following method, it will be productive there?
And then they say that while loading a table from a text file, use LOAD DATA INFILE works on average 20 times faster than INSERT.

SELECT * FROM tbl1 INFO OUTFILE '/tmp/tbl1.txt';
LOAD DATA INFILE '/tmp/tbl1.txt' INTO TABLE tbl2;

instead of:
INSERT INTO tbl2 SELECT * from tbl1;
- Miracle.Bahringer62 commented on October 3rd 19 at 04:48
>> And what tell about working with such a large amount of data?
Describe the objective. Need to know how this data is further used. I, for example, it is difficult to imagine why 300mln copy from one place to another, which is 100 times less data.
>> Tell me, what do you say about the following method, it will be productive there?
Test, but I think that will not happen. The large costs associated with creating and reading the file (parsing). - April_Wucker commented on October 3rd 19 at 04:51
I'm sorry, I mixed up the amount of data in the tables. - Miracle.Bahringer62 commented on October 3rd 19 at 04:54
October 3rd 19 at 04:41
At ofsayte there is a lot of useful information on this topic. And Google query speedup mysql insert select can help

If you use InnoDB, then add the header, else SET autocommit=0; and add after inserts COMMIT;
October 3rd 19 at 04:43
Access FS have?
If Yes. it
a) you Can copy the database files (myd,frm,myi), first stopping the database.
b) you Can use mysqlhotcopy (which is almost the same as the point A, only smarter and without stopping the entire database).

p.s.: If the data from one table to another, it is necessary to add, then do the above to "release" the data from the worksheet to a temporary that would be working already to continue working while there is a long copy using insert into
October 3rd 19 at 04:45
In InnoDB, you can enable the transaction and to record all in once, then optimizes the work of the journal and increases the speed. Disabling foreign keys will also speed up, but the way to do it if you are confident in their actions.
October 3rd 19 at 04:47
The first table contains about 3 million records, the second a little more than 350 million records

I think, it is better to rename the tables by swapping them. Insert 3 is easier than the 350. If you need to constantly chase 350кк records from one table to another — probably you have something wrong with the architecture, and should think in the direction of the triggers or something caching on the way from data to musculo. Like Menkes now doing a simple database, but I do not have any information. it is necessary to look at the application where to put.
I'm sorry, I mixed up the amount of data in the tables. - April_Wucker commented on October 3rd 19 at 04:50
then change the table type to Inno, because now your whole table Loshitsa on inserts. - Miracle.Bahringer62 commented on October 3rd 19 at 04:53
And why change a table type, I will lose in productivity. And the fact that she lechitsya — it does not matter now. - Miracle.Bahringer62 commented on October 3rd 19 at 04:56
and posted show create table here, pliz. - April_Wucker commented on October 3rd 19 at 04:59
And in some cases MyISAM is winning in speed, InnoDB for current versions of MySQL? www.mysqlperformanceblog.com/2013/05/22/mysql-and-... - Miracle.Bahringer62 commented on October 3rd 19 at 05:02
October 3rd 19 at 04:49
We have a similar problem with a comparable amount of data. SELECT + LOAD on InnoDB takes minutes (SSD + ~120Gb RAM). The keys are not switchable, like autocommit - IMHO the atomic queries in this sense is not, indices and so will be updated only after recording all data. Do LOAD the IGNORE to ignore duplicates. Another preimushestvo SELECT + LOAD before the SELECT INSERT - no lock-on time SELECT.

Find more questions by tags DatabasesSQLMySQL