views:

598

answers:

1

We're having a problem where a mysqldump script is spending 90% of it's time populating a small handful of the tables it deals with. Eliminating FK's and indexes eliminates the speed problem, but is not an acceptable solution.

The dump script DOES have:

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

Can we expect any different behavior from "ALTER TABLE foo DISABLE KEYS"?

Also, being an alter table statement, is "disable keys" permanent until I re-enable? i.e., can I disable keys from one mysql session and have it effect the import issued from another session? Or is it a session-scoped statement?

+1  A: 

Yes, you should get significant benefits out of DISABLE KEYS. It isn't session-scoped, it's a table property, so your keys will be dead for everybody until you do ENABLE KEYS.

chaos
Actually, not so much. DISABLE KEYS doesn't seem to be improving our import time, yet dropping the keys from the table entirely gives us 20X performance. Suspicious now of max temp file sizes...
Brian Deacon
Hunh. Odd. Do let me know if you find out what's going on there.
chaos