One thing I noticed when working with Firebird is for mass imports, it is almost always much faster to lock the DB, deactivate keys and indexes, perform the mass operations, and reactivate all once you're over: rebuilding the constraints once beats maintaining them on a large set of operations. I suspect the picture could look the same with MySQL. But of course, it would have to be benchmarked. ----- Original Message ----- From: "Morbus Iff" <morbus@disobey.com> To: <development@drupal.org> Sent: Saturday, January 20, 2007 1:00 AM Subject: Re: [development] Referential integrity -- finally? [...]
I'm personally not a fan of it, primarily because it caused me headaches in the past. I had to deal with a rather large import to CiviCRM, which is all InnoDB and foreign keys and, for speed reasons (2 million records), could not use their API. This forced me to add the records to the tables manually. And, since the import data was always changing, I had to code the importer in such a way that it could start over - delete all the import data and prepare the database for a fresh import. Foreign keys added tons and tons of time to this - both for the import (though, I have no true facts to back this up) and for the actual deletion, since the rows had to be deleted in quite a specific way, otherwise the foreign keys would complain about splintered data. [...]