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:nagav@stripped]
> Sent: Tuesday, July 02, 2013 12:48 AM
> To: mysql@stripped
> 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.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql