The particular example given here is unsafe and slow. * Without an ORDER BY, you are not guaranteed that the chunks will be distinct.
* If there are any INSERTs/DELETEs between chunk copies, you will get dups/missing rows for two reasons: the inserted/deleted rows, and the OFFSET is not quite right. * OFFSET requires walking over the skipped rows. As you get farther into the table, this takes longer. That is, you have an ORDER(N**2) operation, not what could be ORDER(N). * If replication is involved, 1M rows is a lot -- there will be noticeable delays where other replication activity is stalled. If you have an AUTO_INCREMENT PRIMARY KEY, then using WHERE id > 1000000 AND id <= 2000000 is a better approach -- Order(N), and chunks guaranteed to be distinct. Still, it is not immune from INSERTs/DELETEs. Replication is fixed by decreasing chunk size (and by avoiding OFFSET). > -----Original Message----- > From: Arjun [mailto:na...@yahoo.com] > Sent: Tuesday, July 02, 2013 12:48 AM > To: mysql@lists.mysql.com > Subject: Re: best way to copy a innodb table > > Well, the easy way to chunk the inserts is by use of limit. Here is what I > used for one of my projects: > > Insert ignore into t1 (f1, f2, f3) > Select f1, f2, f3 from t2 limit 1000000, 1000000 > > Inserts 1M records at a time starting from 1M th record in t2 and you can > keep incrementing this offset as you progress. This will help in > monitoring the table inserts and at the same time move chunks of records > from source table. > > Enjoy! > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
