If you copy a MyISAM table with PRIMARY key, don’t forget to order the rows first

mysql> show create table test_tableG
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE ` test_table ` (
  `user_id` int(10) unsigned NOT NULL,
  `auto_id` int(10) unsigned NOT NULL auto_increment,
  `col_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `col1` int(10) unsigned NOT NULL default '0',
  `col2` float NOT NULL,
  `col3` float NOT NULL,
  `col4` int(10) unsigned default NULL,
  `col5` int(10) unsigned default NULL,
  `col6` varchar(255) default NULL,
  PRIMARY KEY  (`user_id`,`auto_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
 
mysql> CREATE TABLE test_table_ordered LIKE test_table;
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO test_table_ordered  SELECT * FROM test_table ORDER BY user_id,auto_id;
Query OK, 22391869 rows affected (10 min 37.33 sec)
Records: 22391869  Duplicates: 0  Warnings: 0
 
mysql>
mysql>
mysql>
mysql> CREATE TABLE test_table_NOTordered LIKE test_table;
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO test_table_NOTordered  SELECT * FROM test_table;
Query OK, 22391869 rows affected (1 hour 18 min 38.50 sec)
Records: 22391869  Duplicates: 0  Warnings: 0
 
 
mysql>
mysql>
mysql> CREATE TABLE test_table_orderedCOPY LIKE  test_table_ordered;
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO test_table_orderedCOPY  SELECT * FROM test_table_ordered;
Query OK, 22391869 rows affected (3 min 19.96 sec)
Records: 22391869  Duplicates: 0  Warnings: 0
 
mysql>
mysql>
mysql> CREATE TABLE test_table_NOTorderedCOPY LIKE  test_table_NOTordered;
Query OK, 0 rows affected (0.01 sec)
 
mysql> INSERT INTO test_table_NOTorderedCOPY  SELECT * FROM test_table_NOTordered;
Query OK, 22391869 rows affected (1 hour 19 min 19.63 sec)
Records: 22391869  Duplicates: 0  Warnings: 0