MySQL stuck on "Repair by Sorting" (ENABLE KEYS)

I have a script that updates localhost, then dumps the updated results to remotehost:

mysqldump --skip-tz-utc --no-create-info --insert-ignore --host=localhost -u u -ppass db table --where=\"timeStamp > FROM_UNIXTIME( $time )\" | mysql -h remote -u u -ppass 开发者_StackOverflow社区db

With 20 records, the update to localhost is very quick (a few seconds) but the dump to remotehost is taking over 4 minutes...when I look at mysql workbench, it says the state of remote host is "Repair by Sorting" and the Info column is "/*!40000 ALTER TABLE 'table' ENABLE KEYS */".

What does this message mean (and why is it taking so long to dump to remotehost with so few records)?

Thanks!

mysqldump is disabling indexes, inserting the records, and re-enabling the indexes. This means it affects the entire table, including the many more records I expect are there based on the time.

Add --skip-disable-keys to the arguments for mysqldump and that should stop happening.

The mysqldump command will do three things to a table during the reload:

1. DISABLE KEYS;
2. multiple INSERTs...
3. ENABLE KEYS;

When you disable keys, it actually disables non-unique indexes.

Primary Keys and Unique Keys are loaded immediately.

Once ENABLE KEYS is started, all non-unique indexes are then build using 'Repair By Sorting';

If you bypass DISABLE KEYS and ENABLE KEYS, you will make things much worse, because the Primary Keys, Unique Keys, and Non-Unique Keys are built row by row and that's internally a messier operation for MySQL.

Since so little data is being added, doing --skip-disable-keys as 'Autocracy' suggested is both wise and concise.

BTW this only applies to MyISAM tables. InnoDB ignores DISABLE KEYS and ENABLE KEYS.